SQL Constraints are rules used to limit the type of data that can be stored into a table, to maintain the accuracy and integrity of the data inside table.
Constraints can be divided into two types,
- Column level constraints: limits only column data
- Table level constraints: limits whole table data
Constraints are used to make sure that the integrity of data is maintained in the database. The NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT are the most used constraints that can be applied to a table.
1. NOT NULL Constraint:
NOT NULL constraint restricts a column from having a NULL value. Once *NOT NULL* constraint is applied to a column, you cannot store null value to that column. It enforces a column to contain a proper value.
Example using NOT NULL constraint
CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);
2. UNIQUE Constraint:
UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data.
Example using UNIQUE constraint when creating a Table
CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);
3. Primary Key Constraint:
The primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain a null value.
Example using PRIMARY KEY constraint
CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);
4. Foreign Key Constraint:
FOREIGN KEY is used to relate two tables. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Example using FOREIGN KEY constraint at Table Level
CREATE TABLE Orders
(
O-Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
5. CHECK Constraint:
CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database.
Example using CHECK constraint
create table Student(s_id int NOT NULL CHECK(s_id > 0), Name varchar(60) NOT NULL, Age int);
