Updated March 17, 2023

Introduction to SQL Constraints

SQL Constraints are the guidelines used to create restrictions to be implemented on the contents of the table or database so that the records should obey these rules in order to be placed in that table. The guidelines are designed by the business personnel or the database architect in an attempt to organize the data in the database, which can give assurance to flexible future upgrades. These constraints are designed carefully and prudently, as it results in the overall smart design of the database.

In SQL, we have many different kinds of constraints. Let us look at the following few constraints in this article.

NOT NULL
CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY
DEFAULT

Different Kinds of SQL Constraints

Below given are different kinds:

1. NOT NULL Constraint

This constraint is used when you do not want any value in that particular column to be a Null value. This means that we cannot insert a Null value for that column while inserting a new row in the table. Therefore, every field in this column always has a non-Null value. A null value means that a particular field has been left empty, and values such as zero or blank space do not come under Null values.

Let us look at an example to create a table called Employee, having 5 columns, where empid, name, and mobile columns do not accept NULL values.

CREATE TABLE Employee (
empid INT NOT NULL,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL,
address VARCHAR(20)
);

2. CHECK Constraint

This constraint limits the values that can be entered in that particular column of the table. To understand this better, let us take the example of passing marks in an exam. The range of values for these marks can only be from 35 to 100. To ensure that only values in this range are entered, we can create a CHECK constraint.

Let us look at an example of creating a CHECK constraint on the salary of employees.

CREATE TABLE Employee (
empid INT NOT NULL,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL,
salary INT CHECK (salary >= 15000 AND salary <= 30000),
address VARCHAR(20)
);

3. UNIQUE Constraint

This constraint is applied to ensure that the particular column accepts only unique values, and repetitive values are not allowed with such a constraint on the column. We can create multiple UNIQUE constraints on various columns in a table. A UNIQUE constraint allows NULL values to be entered.

Let us look at an example of enforcing the UNIQUE constraint. In this example, we are creating a column called mobile in the table Employee, which is to be unique and cannot accept the same mobile number twice.

CREATE TABLE Employee (
empid INT NOT NULL,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(20)
);

4. PRIMARY KEY Constraint

This constraint is used to identify a particular column or a group of columns that can uniquely identify a row in the table. With the PRIMARY KEY constraint in place, any row cannot have a duplicate value. We cannot have NULL as the value for such a column. Even though both a PRIMARY KEY constraint and a UNIQUE constraint impose that the values are unique, we use a UNIQUE constraint when we do not want to declare the column as Primary Key but still want the values in that column to be unique. We can have only a single PRIMARY KEY column or a group of columns in a table, but we can declare many individual columns UNIQUE.

Let us look at an example of the Employee table, creating unique employee IDs; therefore, we can declare the empid column to be the PRIMARY KEY.

CREATE TABLE Employee (
empid INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(20)
);

5. FOREIGN KEY Constraint

This constraint helps the data in one table to establish a relationship with the data in another table in the database. Foreign Key can be a single column or a set of columns. For example, let us consider two tables, Employee and Departments. Suppose we a column called depicted in Employee and a departed in Departments. Then, we can reference the departed of Employee to the departed of Departments if the columns match. In this case, the column in Employee becomes a foreign key reference to the column in the Department table, which is a primary key.

CREATE TABLE Employee (
empid INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(20),
depicted INT FOREIGN KEY REFERENCES Department(depicted)
);

6. DEFAULT Constraint

This constraint is used to specify the default value for a particular column in the table. This way, if there is no value inserted for that column explicitly, the database engine can always refer to the default value specified and insert that in the column. If we have inserted a column with the constraint NOT NULL and the constraint DEFAULT, we do not need to define a default value explicitly. Even without giving a default value, the particular row will be inserted in the table.

Let us look at an example where we have entered the place in the address as default to have the value ‘India’.

CREATE TABLE Employee (
empid INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(20) DEFAULT ‘India’,
depicted INT FOREIGN KEY REFERENCES Department(depicted),
);

Conclusion

SQL constraints help the developer by specifying restrictions and rules for the data that is to be inserted in the table. Constraints can be applied at the column level, just to the particular column or at the table level, where the constraints are applied to the complete table. These constraints restrict the kind of information that can be inserted into the table. This guarantees the correctness and consistency of the data in the table. In case of any violations of the rules specified by the constraints, the action is terminated.