What are Constraints in SQL | Types, Syntax and Examples
Summary: You will learn in this article, what are Constraints in SQL and their types such as Column level constraints, and Table level constraints. Also discuss all constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT. These types of Constraints in SQL play an important role, Let’s understand What are Constraints in SQL with their types, syntax, and examples. I hope you will enjoy it!
What are Constraints in SQL?
SQL constraints are rules that you can apply to the data in a table. These constraints allow you to restrict only specific data that meets the regulations to go to a table. Furthermore, to put it simply, only if the data meets the constraint’s rules, the insert operation will be successful, or else it’ll be aborted.
SQL Constraints help to maintain the accuracy, integrity, and reliability of a table’s data.
Furthermore, it can create them at a column or table level. suppose that if you declare constraints at the column level, then it will apply them to a single column. On the other hand, if you declare them at the table level, then it’ll implement them in more than one column.
You can create constraints in DBMS while creating a table using the CREATE TABLE command or later using the SQL ALTER TABLE command. Thus, if you make a constraint with the ALTER TABLE command, the creation will only be successful if all the existing records or data meets the constraint rules.
Constraints in SQL Example:
When we influence the information of a bank account, we need to have a constraint that may facilitate us to uniquely identify all the customers associated with the bank.
Thus, we need constraints to meet all these industrial requirements. For the use case of a bank, we will be using the SQL UNIQUE constraint to keep the identification number or the account number different for every user of the banking system.
What Are The Types of Constraints in SQL
Basically, constraints can be divided into the following two types such as,
Column level constraints: Limits only column data.
Table level constraints: Limits whole table data.
These constraints are used to ensure that the integrity of data is maintained in the database. The given below constraints are used that can be applied to a table.
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
1) NOT NULL Constraint
By default, a column can hold NULL values. If you don’t want a column to have a NULL value, then use the NOT NULL constraint.
- It will restrict a column from having a NULL value.
- We can use SQL ALTER statement and MODIFY statement to specify this constraint.
- Note: It cannot be defined at table level.
Example Using NOT NULL Constraint:
|
1 2 3 4 5 |
CREATE TABLE Students_Details ( Stu_Id int NOT NULL, Stu_Name varchar(50), Stu_Age int ); |
The above SQL query will declare that the Stu_Id field of the Students_Details table will not take NULL value.
Suppose that if you want to alter the table after it has been created, then we can use the SQL ALTER command for it:
|
1 2 |
ALTER TABLE Students_Details MODIFY Stu_Id int NOT NULL; |
2) UNIQUE Constraint
It makes sure that a column will only have unique values. Furthermore, a SQL UNIQUE constraint field can’t have any duplicate data.
- It prevents two records or duplicates records from having identical values in a column
- We can use SQL ALTER statement and MODIFY statement to specify this constraint.
Example of UNIQUE Constraint:
Here we have need to CREATE query to create a table, which will have a column Stu_Id with unique values.
|
1 2 3 4 5 |
CREATE TABLE Students_Details ( Stu_Id int NOT NULL, Stu_Name varchar(50), Stu_Age int NOT NULL UNIQUE ); |
The above SQL query will declare that the Stu_Id field of the Students_Details table will only have unique values and won’t take NULL values.
Furthermore, if you want to alter the table after it has been created, then we can use the SQL ALTER command for it:
|
1 2 |
ALTER TABLE Students_Details MODIFY Stu_Age INT NOT NULL UNIQUE; |
The above SQL query specifies that Stu_Id field of Students_Details table will only have unique value.
3) Primary Key Constraint
The primary key constraint in SQL uniquely identifies each record/data in a database. Furthermore, a Primary Key (FK) must contain a unique value and it never contains a null value. Usually, Primary Key is utilized to index the data inside the table.
Example: PRIMARY KEY constraint at Table Level
|
1 2 3 4 |
CREATE table Students_Details ( Stu_Id int PRIMARY KEY, Stu_Name varchar(50) NOT NULL, Stu_Age int); |
The above SQL query will creates a PRIMARY KEY on the Stu_Id.
Example: PRIMARY KEY constraint at Column Level
|
1 2 |
ALTER table Students_Details ADD PRIMARY KEY (Stu_Id); |
The above SQL query will creates a PRIMARY KEY on the Stu_Id.
4) Foreign Key Constraint
Foreign Key (FK) is used to establish the relationship between two tables. The relationship between the two tables matches the Primary Key(FK) in one of the tables with a Foreign Key in the 2nd table.
This is also known as a referencing key.
We can use SQL ALTER statement and ADD statement to specify this constraint.
To understand the FOREIGN KEY(FK), let’s see its use, with help of the given below tables:
Table1: Customers
| Cust_ID | Cust_Name | Cust_City |
|---|---|---|
| 1101 | Marco | Greater Noida |
| 1102 | Steffan | Delhi |
| 1103 | Adelina | Lucknow |
| 1104 | Cathenna | Ghaziabad |
| 1105 | Marielia | Kolkata |
Table2: Orders
| Order_ID | Product_Name | Cust_ID |
|---|---|---|
| 101 | Air purifier | 1104 |
| 102 | Air conditioner | 1103 |
| 103 | Alarm clock | 1105 |
| 104 | Bread maker | 1101 |
| 105 | Blender | 1102 |
In Customers table, Cust_ID is the primary key(PK) which is set as foreign key(FK) in Orders table.
The value that is entered in Cust_ID which is set as foreign key in Orders table must be present in Customers table where it is set as primary key.
This prevents invalid data to be inserted into Cust_ID column of Orders table. Furthermore, if you try to insert any incorrect data, then DBMS will return error and will not allow you to insert the data.
FOREIGN KEY constraint at Table Level
|
1 2 3 4 5 |
CREATE table Orders( Order_ID int PRIMARY KEY, Product_Name varchar(50) NOT NULL, Cust_ID int FOREIGN KEY REFERENCES Customers(Cust_ID) ); |
Cust_ID in table Orders is made as foriegn key(FK), which is a reference of Cust_ID column in Customers table.
FOREIGN KEY constraint at Column Level
|
1 2 |
ALTER table Orders ADD FOREIGN KEY (Cust_ID) REFERENCES Customers(Cust_ID); |
The Behavior of Foreign Key (FK) Column on Delete
There are two methods to maintain the integrity of data in the Child table when a specific record is deleted in the main table. Furthermore, when two tables are connected with an FK-Foreign key, and certain data in the main table is deleted, for which a record or data exists in the child table, then we must have a few mechanisms to save the integrity of data in the child table.
- NULL (On Delete Null): This will set all the values in that record or data of the child table as NULL, for which the value of the FK-foreign key is deleted from the main table.
- CASCADE (On Delete Cascade): This will remove the record or data from the child table if that value of the FK-foreign key is deleted from the main table.
Suppose that, if we don’t use any of the above, then we can’t delete data from the main table for which data in the child table exists. Thus, we will get an error if we attempt to do so.
5) CHECK Constraint
A SQL CHECK constraint is utilized to restrict the value of a column between a range. Furthermore, it performs a check on the values, before storing them in the database. It’s just like condition checking before saving records or data into a column.
Using CHECK constraint at Table Level
|
1 2 3 4 5 |
CREATE table Students_Details( Stu_Id int NOT NULL CHECK(Stu_Id > 0), Stu_Name varchar(50) NOT NULL, Stu_Age int ); |
Stu_Id value to be greater than zero.
Using CHECK constraint at Column Level
|
1 |
ALTER table Students_Details ADD CHECK(Stu_Id > 0); |
6) DEFAULT Constraint
The DEFAULT constraint is used to assign a default value to the left empty fields. Furthermore, if you neither need a user to enter empty fields nor need to implement the NOT NULL constraint, the DEFAULT constraint can come in handy. Therefore, it is also useful to provide a default value to a field if set to NOT NULL. Thus, this will prevent the constraint error, as the field will be given the default value & will not remain NULL.
Example of DEFAULT Constraint
|
1 2 3 4 5 6 |
CREATE TABLE Students_Details( Stu_Id int PRIMARY KEY, Stu_Name varchar(50) NOT NULL, Stu_Age int NOT NULL DEFAULT 18 ); |
Why Use Constraints in SQL?
When you will use the SQL constraints then it ensures that:
The records or data are correct (appropriate to the column).
Data or records are of the best quality (no data is missing or of the wrong data type).
The applications that input the data must ensure the rules/constraints we set.
By using constraints in SQL, we save time spent checking that the data or records have the needed correctness. Furthermore, constraints make sure that if future INSERTs or UPDATEs don’t conform to the rules, they will not be added to our database.
Conclusion:
SQL constraints facilitate the developer by specifying restrictions and rules for the information or data that is to be inserted in the table. Furthermore, Constraints can be applied at the column level, simply to the particular column, or at the table level, where the constraints are applied to the complete table. Thus, these constraints restrict the kind of information or data that can be inserted into the table. This guarantees the correctness & consistency of the records in the table. Therefore, in case of any violations of the rules specified by the constraints, the action is terminated.
About Author
