SQL Constraints

Constraints are the rules that we can apply on the type of data in a table. That is, we can specify the limit on the type of data that can be stored in a particular column in a table using constraints.

The available constraints in SQL are:

• NOT NULL: This constraint tells that we cannot store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column any more.
• UNIQUE: This constraint when specified with a column, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated.
• PRIMARY KEY: A primary key is a field which can uniquely identify each row in a table. And this constraint is used to specify a field in a table as primary key.
• FOREIGN KEY: A Foreign key is a field which can uniquely identify each row in a another table. And this constraint is used to specify a field as Foreign key.
• CHECK: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition.
• DEFAULT: This constraint specifies a default value for the column when no value is specified by the user.

How to specify constraints?
We can specify constraints at the time of creating the table using CREATE TABLE statement. We can also specify the constraints after creating a table using ALTER TABLE statement.
Syntax:
Below is the syntax to create constraints using CREATE TABLE statement at the time of creating the table.

CREATE TABLE sample_table
(
column1 data_type(size) constraint_name,
column2 data_type(size) constraint_name,
column3 data_type(size) constraint_name,
....
);

sample_table: Name of the table to be created.
data_type: Type of data that can be stored in the field.
constraint_name: Name of the constraint. for example- NOT NULL, UNIQUE, PRIMARY KEY etc. 

Let us see each of the constraint in detail.

1. NOT NULL
   If we specify a field in a table to be NOT NULL. Then the field will never accept null value. That is, you will be not allowed to insert a new row in the table without specifying any value to this field.
   For example, the below query creates a table Student with the fields ID and NAME as NOT NULL. That is, we are bound to specify values for these two fields every time we wish to insert a new row.

   CREATE TABLE Student
   (
   ID int(6) NOT NULL,
   NAME varchar(10) NOT NULL,
   ADDRESS varchar(20)
   );
   

2. UNIQUE
   This constraint helps to uniquely identify each row in the table. i.e. for a particular column, all the rows should have unique values. We can have more than one UNIQUE columns in a table.
   For example, the below query creates a tale Student where the field ID is specified as UNIQUE. i.e, no two students can have the same ID.

   CREATE TABLE Student
   (
   ID int(6) NOT NULL UNIQUE,
   NAME varchar(10),
   ADDRESS varchar(20)
   );
   

3. PRIMARY KEY
   Primary Key is a field which uniquely identifies each row in the table. If a field in a table as primary key, then the field will not be able to contain NULL values as well as all the rows should have unique values for this field. So, in other words we can say that this is combination of NOT NULL and UNIQUE constraints.
   A table can have only one field as primary key.Below query will create a table named Student and specifies the field ID as primary key.

   CREATE TABLE Student
   (
   ID int(6) NOT NULL UNIQUE,
   NAME varchar(10),
   ADDRESS varchar(20),
   PRIMARY KEY(ID)
   );
   

4. FOREIGN KEY
   Foreign Key is a field in a table which uniquely identifies each row of a another table. That is, this field points to primary key of another table. This usually creates a kind of link between the tables.
   Consider the two tables as shown below:
   Orders

   O_ID	ORDER_NO	C_ID
   1	2253	3
   2	3325	3
   3	4521	2
   4	8532	1

   Customers

   C_ID	NAME	ADDRESS
   1	RAMESH	DELHI
   2	SURESH	NOIDA
   3	DHARMESH	GURGAON

   As we can see clearly that the field C_ID in Orders table is the primary key in Customers table, i.e. it uniquely identifies each row in the Customers table. Therefore, it is a Foreign Key in Orders table.
   Syntax:
   

   CREATE TABLE Orders
   (
   O_ID int NOT NULL,
   ORDER_NO int NOT NULL,
   C_ID int,
   PRIMARY KEY (O_ID),
   FOREIGN KEY (C_ID) REFERENCES Customers(C_ID)
   )
   

5. CHECK
   Using the CHECK constraint we can specify a condition for a field, which should be satisfied at the time of entering values for this field.
   For example, the below query creates a table Student and specifies the condition for the field AGE as (AGE >= 18 ). That is, the user will not be allowed to enter any record in the table with AGE < 18. 

   CREATE TABLE Student
   (
   ID int(6) NOT NULL,
   NAME varchar(10) NOT NULL,
   AGE int NOT NULL CHECK (AGE >= 18)
   );
   

6. DEFAULT
   This constraint is used to provide a default value for the fields. That is, if at the time of entering new records in the table if the user does not specify any value for these fields then the default value will be assigned to them.
   For example, the below query will create a table named Student and specify the default value for the field AGE as 18.

   CREATE TABLE Student
   (
   ID int(6) NOT NULL,
   NAME varchar(10) NOT NULL,
   AGE int DEFAULT 18
   );