What are SQL Constraints?

SQL constraints are rules applied to the columns in a database table to enforce data integrity, consistency, and accuracy. Constraints ensure that the data entered into a database meets specific criteria. Here’s a brief overview of the most common SQL constraints:

1. NOT NULL: Ensures that a column cannot have a NULL value, meaning it must always contain a value.
2. UNIQUE: Ensures that all values in a column are unique, preventing duplicate entries.
3. PRIMARY KEY: Uniquely identifies each record in a table. A primary key combines UNIQUE and NOT NULL constraints. Each table can have only one primary key, which can consist of one or more columns.
4. FOREIGN KEY: Ensures referential integrity by linking a column in one table to the primary key in another table. It enforces that the values in the foreign key column match values in the referenced primary key column.
5. CHECK: Ensures that all values in a column satisfy a specific condition or logical expression. For example, a CHECK constraint can ensure that age is always greater than zero.
6. DEFAULT: Automatically assigns a default value to a column if no value is specified during an insert operation.
7. INDEX: Although not strictly a constraint, it is used to speed up the retrieval of rows by creating indexes on columns.

Constraints help maintain the accuracy, reliability, and integrity of the data in a relational database.

Constraints in SQL are used to enforce rules on the data stored within a table, ensuring accuracy, integrity, and reliability. They restrict the type of data that can be entered into a table and prevent invalid data from being inserted. When a constraint rule is violated during a data operation, the action will fail and not be completed.

Constraints can be defined at either the column level or the table level:

• Column-Level Constraints: These apply to a single column, limiting what values can be entered into that specific field.
• Table-Level Constraints: These apply to multiple columns and are typically used when the rule involves more than one column.

For a deeper understanding of SQL constraints with examples, consider exploring a comprehensive SQL course.

The Syntax of SQL Constraints

Given below is the syntax of SQL constraints.

Table Creation:

CREATE TABLE table_name (

??? column1 datatype constraint,

??? column2 datatype constraint,

??? column3 datatype constraint,

??? ....

);

7 Different Types of Constraints in SQL

In this section, we will take a deeper look into the main types of SQL constraints and provide examples of how to create them.

1. NOT NULL Constraint in SQL

If you want to stop blank or null values from being entered into a certain column of an SQL table, the NOT NULL constraint can be used. This allows individuals to input a value when creating new rows in the?database?and prevents empty fields in this particular column. It is useful for situations where there must always be data within selected columns.

The below query creates a table Student with the fields ID and NAME set as NOT NULL, meaning that values must be provided for these two fields each time we attempt to insert a new row.

Table Creation:

?Inserting Records:

?Retrieving Data:

2. UNIQUE Constraint in SQL

The UNIQUE constraint prevents the same value from being entered twice into a specified column. For example: if you have a table called “Product” with columns, such as “Product_ID,” “Product_Name,” and Barcode”, then applying the UNIQUE constraint to the barcode field will ensure that each product has its distinct barcode number. Say someone attempts to insert an item using an already existing/used bar code. In this case, the system won’t accept this entry and display an error message instead. In other words, no duplicates are allowed for entries restricted by a unique constraint.

Table Creation:

CREATE TABLE Product (

? Product_ID int NOT NULL,

? Product_Name varchar(50) NOT NULL,

? Barcode varchar(20) UNIQUE

);

?Inserting Records:

INSERT INTO Product VALUES (1, 'Apple', '123456789');

INSERT INTO Product VALUES (2, 'Banana', '987654321');

INSERT INTO Product VALUES (3, 'Orange', '246813579');

?

-- Attempting to insert a duplicate barcode

INSERT INTO Product VALUES (4, 'Grapes', '123456789');

?

We create a “Product” table in the above code with columns for product ID, name, and barcode. We make sure that each value in the “Barcode” column is distinct by adding a UNIQUE constraint to it. Three products are then inserted into this table containing unique barcodes. If an attempt was made to insert another product that has already been registered (with its own ‘123456789’ barcode), it would be prevented from entering due to a violation of the previously enforced UNIQUE rule, resulting in an error message being displayed instead.

3. PRIMARY KEY Constraint

A primary key of a database table serves as a unique identifier for each row. It must have distinct values and cannot be null, combining the NOT NULL and UNIQUE constraints. This means no two rows in the same table can share identical values for their respective primary keys, plus all entries must contain an actual value instead of being blank or unknown.

Table Creation:

CREATE TABLE Student (

??ID int PRIMARY KEY,

??Name varchar(50) NOT NULL,

??Age int,

??Email varchar(50) UNIQUE

);

We created a “Student” table that has four columns – ID, Name, Age, and Email. We used the PRIMARY KEY constraint to make sure each row in this table has its unique identifier in the form of an ‘ID’ number since no two rows can contain identical values for it. This is achieved by making this field both NOT NULL (no null values) and UNIQUE (each value must be different from all others).

4. FOREIGN KEY Constraint

A foreign key is a type of field in one table that links it to another by referencing the primary key of the other. This allows for each row in both tables to be identified uniquely through this link between them. For instance:?

In this example, the “Employees” table has a foreign key field, which is the “DepartmentID”. This relates to the primary key in another table called “Departments”. Now, this allows us to create an association between these two tables. To do this, we will use this SQL code when creating both of them with a statement specifying that the link should be established by assigning “DepartmentID” as its foreign key.

Table Creation:

CREATE TABLE Departments (

? DepartmentID int PRIMARY KEY,

? DepartmentName varchar(50)

);

?

CREATE TABLE Employees (

? EmployeeID int PRIMARY KEY,

? EmployeeName varchar(50),

? DepartmentID int,

? FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)

);

Two tables, “Departments” and “Employees”, are created by the code. The primary key of the Departments table is called DepartmentID. What links the two tables together is a foreign key that exists in Employees which references back to DepartmentID from Departments. This ensures any value for DepartmentID present in Employees points to a valid record from Departments.

5. CHECK Constraint

The CHECK constraint is one of the types of constraints in SQL that can be used to make sure that data meets certain requirements before it is inserted into a table. For example, if we have an Employees table and want only salaries between $20,000 and $100,000 to be stored in the salary column of this table, we could use the CHECK constraint with our CREATE TABLE statement for the Employee’s Table like this:

Table Creation:

CREATE TABLE Employees (

??? EmployeeID int PRIMARY KEY,

??? Name varchar(50) NOT NULL,

??? Salary decimal(10, 2) CHECK (Salary >= 20000 AND Salary <= 100000)

);

?Inserting Records:

INSERT INTO Employees VALUES (1, 'John Doe', 40000.00);

INSERT INTO Employees VALUES (2, 'Jane Smith', 15000.00);

INSERT INTO Employees VALUES (3, 'Mark Johnson', 90000.00);

INSERT INTO Employees VALUES (4, 'Emily Brown', 110000.00);

Retrieving Data:

SELECT * FROM Employees;

In this example, we created a table called ‘Employees’ with columns for EmployeeID, Name, and Salary. The column named Salary has rules set that the salary must be between $20,000 and $100,000. We then insert four records into this ‘Employees’ table. Two records are successful (with a value of $40k & 90k) but two fail because they need to meet the CHECK constraint’s conditions ($15k & 110K). Lastly, we view all existing data in our Employees Table to check which ones follow our rule.

6. DEFAULT Constraint

When you use the DEFAULT constraint with a SQL column, it will automatically insert the specified default value when no other value is provided. This means that if someone tries to add data without specifying any values for this particular column, then the DEFAULT value set up by you will be used instead. It helps ensure columns have some value in them and can help prevent unexpected empty fields from appearing in your database tables.

Table Creation:

CREATE TABLE Employees (

??? EmployeeID int PRIMARY KEY,

??? Name varchar(50) NOT NULL,

??? Department varchar(50),

??? Salary decimal(10, 2) DEFAULT 50000.00

);

Inserting Records:

INSERT INTO Employees (EmployeeID, Name, Department, Salary)

VALUES (1, 'John Doe', 'Engineering', 70000.00);

?

INSERT INTO Employees (EmployeeID, Name, Department)

VALUES (2, 'Jane Smith', 'Marketing');

?

INSERT INTO Employees (EmployeeID, Name, Department, Salary)

VALUES (3, 'Alice Johnson', 'Finance', 60000.00);

?

INSERT INTO Employees (EmployeeID, Name)

VALUES (4, 'Bob Brown');

?

Retrieving Data:

SELECT * FROM Employees;

We create a table called “Employees” with columns for EmployeeID, Name, Department, and Salary. The Salary column is given a default value of $50,000 using the DEFAULT constraint. Next, we insert four records into the table, each with varying levels of detail (e.g., the first record specifies all fields, while the last record specifies only the EmployeeID and Name). When we retrieve all records from the Employees table, the default salary of $50,000 is automatically applied where no specific value was provided.

?

7. CREATE INDEX Constraint

In SQL, the CREATE INDEX statement is used to speed up data retrieval by allowing the database system to quickly locate and access data. It works similarly to an index in a book: instead of reading through every single page, you can quickly find information by looking at the index. By creating an index on specific columns or entire tables, queries involving those columns can be processed much faster, significantly improving performance.

Here’s a more readable explanation of how indexing works and an example of the CREATE INDEX statement:

• How It Works: When you create an index, you instruct the database to maintain an ordered structure for the indexed columns. This allows the database engine to quickly find and access rows that match a query’s criteria, much like using page numbers in a book’s index.
• When to Use Indexes: Indexes are especially useful on columns that are frequently searched, filtered, or used in JOIN operations. However, keep in mind that while indexes speed up data retrieval, they can slightly slow down INSERT, UPDATE, and DELETE operations due to the additional maintenance required.

Here’s an example of how to create an index in SQL:

CREATE TABLE Students (

? StudentID int PRIMARY KEY,

? Name varchar(50) NOT NULL,

? Course varchar(50),

? Grade decimal(5, 2)

);

?

INSERT INTO Students VALUES (1, 'John Smith', 'Mathematics', 85.5);

INSERT INTO Students VALUES (2, 'Emma Johnson', 'History', 92.0);

INSERT INTO Students VALUES (3, 'Michael Brown', 'Science', 78.8);

INSERT INTO Students VALUES (4, 'Sophia Davis', 'English', 88.2);

?

CREATE INDEX Students_Index

ON Students (Name, Course, Grade);

?

SELECT * FROM Students;

Conclusion

SQL is a powerful language used for managing and interacting with databases. It allows you to organize and structure data while enforcing specific rules, known as constraints, to ensure data integrity and consistency. Common constraints in SQL include NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY, which play a vital role in maintaining data accuracy. Additionally, features like CHECK, DEFAULT, and CREATE INDEX help control data validation and optimize performance when accessing or retrieving information in a database. These tools collectively ensure that databases remain reliable, efficient, and secure.