SQL Constraints

Constraints in SQL are rules used in limiting the type of data that can go into a table, to maintain the accuracy and integrity of the data inside the table.

They are grouped into two types,

• Table level constraints: They are used to limit whole table data
• Column level constraints: They are used to limit only column data.

In SQL, constraints are used to ensure that the integrity of data is maintained in the database. Below is a list of the most used constraints that can be applied to a table.

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

NOT NULL Constraint

However, a column can hold a NULL value by default. But, there is no need for a column to have a NULL value, the NOT NULL constraint is used. Take a look at the features of the NOT NULL constraints:

• They restrict a column from having a NULL value.
• They also use ALTER statement and MODIFY statement to specify this constraint.

Note: Constraints cannot be defined at table level.

Example using NOT NULL constraint:

CREATE TABLE Student
(  	s_id int NOT NULL, 
   	name varchar(60), 
   	age  int
);

The query above is used to declare the s_id field of the Student table that will not take NULL value.

But, if there is a need to alter the table after it has been created, then the ALTER command can be used:

ALTER TABLE Student
MODIFY s_id int NOT NULL;

UNIQUE Constraint

The unique constraints are used to make sure that a column will only have unique values. Below are some features of the Unique constraints:

• With the UNIQUE constraint, a column cannot have any duplicate data.
• The UNIQUE constraints prevent two records from having identical values in a column.
• Also, in UNIQUE constraints, we use ALTER statement and MODIFY statement to specify this constraint.

Example of UNIQUE Constraint:

In this case, we will use a simple CREATE query to create a table, which will have a column s_id with unique values.

CREATE TABLE Student
( 	s_id int  NOT NULL, 
  	name varchar(60), 
  	age int  NOT NULL UNIQUE
);

The query above was used to declare the s_id field of the Student table will only have unique values but will not take a NULL value.

However, If there is a need to alter the table after it has been created, then the ALTER command can be used.

ALTER TABLE Student
MODIFY age INT NOT NULL UNIQUE;

The SQL query above specifies that the s_id field of the Student table can only have a unique value.

Primary Key Constraint

Here, the primary key constraint identifies each record in a database. It must contain a unique value and must not contain a null value. However, Primary Key is used to index data inside a table.

PRIMARY KEY constraint at Table Level

CREATE table Student 
(	s_id int PRIMARY KEY, 
	Name varchar(60) NOT NULL, 
	Age int);

The command illustrated above is used to create a primary key on the s_id.

PRIMARY KEY constraint at Column Level

ALTER table Student 
ADD PRIMARY KEY (s_id);

The command above is used to create a primary key on the s_id.

Foreign Key Constraint

The Foreign Key constraints can be used to relate two tables. In addition, a relationship exists between the two tables which matches the Primary Key in one of the tables with a Foreign Key in the second table.

• This attribute is also called a referencing key.
• As said before, the ALTER statement and ADD statement can be used to specify this constraint.

For a better understanding of the FOREIGN KEY, take a look at the following tables below:

Customer_Detail Table

Order_Detail Table

• From the Customer_Detail table, c_id is the primary key which is set as a foreign key in the Order_Detail table. 
• Secondly, the value entered in c_id that is set as a foreign key in Order_Detail table must be present in Customer_Detail table where it is set as a primary key. 
• Lastly, this avoids invalid data to be inserted into c_id column of the Order_Detail table.

In addition, when you try to insert any incorrect data, the DBMS will return an error response and will not allow you to insert the data.

FOREIGN KEY constraint at Table Level

CREATE table Order_Detail(
    order_id int PRIMARY KEY, 
    order_name varchar(60) NOT NULL,
    c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id)
);

In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column in Customer_Detail table.

FOREIGN KEY constraint at Column Level

ALTER table Order_Detail 
ADD FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when a particular record is deleted in the main table. When two tables are connected with Foriegn key, and certain data in the main table is deleted, for which a record exits in the child table, then we must have some mechanism to save the integrity of data in the child table.

1. On Delete Cascade : This will remove the record from child table, if that value of foriegn key is deleted from the main table.
2. On Delete Null : This will set all the values in that record of child table as NULL, for which the value of foriegn key is deleted from the main table.
3. If we don't use any of the above, then we cannot delete data from the main table for which data in child table exists. We will get an error if we try to do so.

CHECK Constraint

In this case, the CHECK constraint is used to restrict the value of a column between a range. The check constraints are used to perform checks on the values before storing them in the database. It is more like condition checking before saving data into a column.

Using CHECK constraint at Table Level

CREATE table Student(
    s_id int NOT NULL CHECK(s_id > 0),
    Name varchar(60) NOT NULL,
    Age int
);

The query above is used to restrict the s_id value to be greater than zero.

Using CHECK constraint at Column Level

ALTER table Student ADD CHECK(s_id > 0);