SQL Constraints: Everything You Need to Know

SQL Constraints Tutorial With Example | Understand SQL Constraints

Constraints are the set of rules enforced on the data columns of the table. These are used to limit the type of data that can go into the table. It ensures the accuracy, integrity, and reliability of the data in the SQL database.

SQL Constraints

SQL Constraints are rules used to limit a type of data that can go into the table to maintain the accuracy and integrity of data inside the table. Constraints in SQL are used to ensure that the data’s integrity is maintained in the database.

Constraints can be divided into the following two types,

  1. Column level constraints: Limits only the column data.
  2. Table-level constraints: Limits whole table data.

See the following most used constraints that can be applied to the table.

  5. CHECK

SQL NOT NULL Constraint

NOT NULL constraint restricts the column from having the NULL value. Once a NOT NULL constraint is applied to the column, you cannot pass the null value to that column. It enforces the column to contain a proper value. Empty will should not be there in that column for every table.

Generally, the ID column in the SQL Table contains the NOT NULL constraint.

See the following query of NOT NULL constraint.

 id int NOT NULL, 
 Name varchar(60) NOT NULL, 
 Song varchar(60)

The above query will declare that the Singer table’s id and Name field will not take NULL value.


We have covered Primary Key in this blog. The primary key is a field in the table, which is used for uniquely identifying the row in the table. If the column has a primary key constraint, it will contain the unique values and will not hold any NULL values.

The following SQL statement creates the table named investors and specifies an id column as a primary key. That means the id field does not allow the NULL or duplicate values.

CREATE TABLE investors (
    name VARCHAR(30) NOT NULL,
    portfolio amount INT,
    fund_name VARCHAR(50)

In the above query, we will create the investors’ table in which the id column is the primary key. You can not add NULL value in the Primary Key column. So, the id column now has two SQL Constraints. One point to remember is that a primary key length cannot exceed more than 900 bytes.

SQL UNIQUE Constraint

The UNIQUE constraint ensures that a field or the column will only have unique values. The UNIQUE constraint field will not have any duplicate data. The unique constraint can be applied at the column or table levels.

SQL UNIQUE Constraint prevents two records from having identical values in a column.

UNIQUE constraint in Table-level

Here we have the simple CREATE query to create the table, with the column app_id with unique values. See the following query.

  app_id int NOT NULL UNIQUE, 
  Name varchar(60), 
  Price int

UNIQUE constraint in Column-level

Here, we have to use the Alter table query to assign the Unique constraint to a particular field.

See the following query.


The above query specifies that the app_id field of the Apps table will only have a unique value.

SQL Foreign Key Constraint

We have also covered the Foreign Key constraint here in this blog. SQL foreign key is used to form a link between two tables, which makes it a referencing key. FOREIGN KEY is used to relate the two tables.

The FOREIGN KEY constraint is also used to restrict the actions that destroy the links between tables. Let’s see its use with the help of the two tables.


i_id investor_Name address
101 KRUNAL Noida
102 ANKIT Delhi
103 RUSHABH Rohtak


e_id employee_age i_id
10 26 101
11 24 103
12 26 102

In the above, there are two tables.

  1. Investors
  2. Employees

The above two tables are connected through the Foreign key called i_id.

The investors’ table has a primary key called i_id, and the Employees table has a foreign key called i_id.

If you try to insert incorrect data, the DBMS will return the error and not allow you to insert any data.

So, it will prevent adding any malicious values are manipulative values. When editing, updating, or deleting the data between those tables, you need to be very careful.

If it goes wrong, the entire system will behave unnaturally and buggy.

Using FOREIGN KEY constraint at Table Level

See the following query.

CREATE table Employees(
    e_id int PRIMARY KEY, 
    employee_age varchar(60) NOT NULL,
    i_id int FOREIGN KEY REFERENCES Investors(i_id)

So, in the above query, we have defined the relationship between the tables using i_id.

The i_id column in the Employees table reference from Investors tables.

Using FOREIGN KEY constraint at Column Level

See the following query.

ALTER table Employees 
ADD FOREIGN KEY (i_id) REFERENCES Investors(i_id);

We have used Alter query to add the Foreign Key constraint to the Employees table.

The behavior of Foreign Key Column on Delete

There are two ways to maintain data integrity in the Child table when the particular record is removed from the main table. First, when the two tables are connected through the Foreign key and specific data in the main table is removed, the record exists in a child table, and we must have some mechanism to save the integrity of the data in the child table.

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

Check Constraint

The CHECK constraint is used to restrict the column’s value between the range.

It performs a check on the values before storing them in the database. It’s like the condition was checked before saving data into the column.

Using CHECK constraint at Table Level

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

The above query will restrict the id value to being higher than zero.

Using CHECK constraint at Column Level

See the following query.

ALTER table Student ADD CHECK(id > 0);

DEFAULT Constraint

The DEFAULT constraint provides the default value to a column when the INSERT INTO statement does not provide the specific value.

Using DEFAULT constraint at Table Level

   ID   INT              NOT NULL,
   ADDRESS  CHAR (100) ,
   SALARY   DECIMAL (18, 2) DEFAULT 100000.00,       

Using DEFAULT constraint at Column Level

If the table has been created already, we can use the Alter table statement to assign the default constraint. See the following query.


That’s it for this tutorial.

Leave A Reply

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.