Constraints in SQL

SQL constraints are used to specify rules for the data in a table. In other words SQL constraints are used to restrict certain type of data that can be inserted into the table.

This ensures the accuracy and reliability of the data that is inserted into the table. If there is any violation between the constraint and the data action, i.e if we try to insert the data which do not satisfy the constraint then the action is aborted.

Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);
We can specify the constraints at time of table creation or after by using ALTER TABLE statement.

Constraints can be of two types viz. column level or table level. Column level constraints apply to a column whereas table level constraints apply to the whole table.

SQL Constraints

Commonly used constraints in SQL are given below:
  • NOT NULL - Ensures that a column cannot have a NULL value
  • UNIQUE - Ensures that all values in a column are different
  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
  • FOREIGN KEY - Uniquely identifies a row/record in another table
  • CHECK - Ensures that all values in a column satisfies a specific condition
  • DEFAULT - Sets a default value for a column when no value is specified

SQL NOT NULL Constraint

In SQL if you do not specify a value for a column then by default it holds a null value.

The NOT NULL constraint ensures that a column will not NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field. If you try to insert such records voilating the constraint, an error message will be displayed.

The following example ensures that the "Rollno", "FirstName", and "LastName" columns will NOT accept NULL values:

Example

CREATE TABLE Student(
    Rollno int NOT NULL,
    FisrtName varchar(255) NOT NULL,
    LastName varchar(255) NOT NULL,
    class varchar(20),
    Age int
); 

NOTE: If the table has already been created, you can add a NOT NULL constraint to a column with the ALTER TABLE statement.

SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY (Discussed Below) constraints provide a guarantee for uniqueness for a column or set of columns.

A primary key already have a UNIQUE constraint.

However, you can specify many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table is allowed.

Defining UNIQUE Constraint While Creating A Table:

The following SQL creates a UNIQUE constraint on the "Rollno" column when the "Student" table is created:

Example

In SQL Server / Oracle / MS Access:
CREATE TABLE Student(
    Rollno int NOT NULL UNIQUE,
    FirstName varchar(255),
    LastName varchar(255),
    class varchar(20),
    Age int
);
In MySQL:
CREATE TABLE Student(
    Rollno int NOT NULL,
    FirstName varchar(255),
    LastName varchar(255),
    class varchar(20),
    Age int,
    UNIQUE (ID)
);

To define a UNIQUE constraint on multiple columns use following syntax:

In MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Student(
    Rollno int NOT NULL,
    FirstName varchar(255),
    LastName varchar(255),
    class varchar(20),
    Age int,
    CONSTRAINT Unique_Person UNIQUE (ID,LastName)
);

Defining UNIQUE Constraint Using ALTER TABLE

To specify a UNIQUE constraint on the "Rollno" column when the table is already created, we use the following statement:

In MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Student
ADD UNIQUE (Rollno);
To name a UNIQUE constraint and to define a UNIQUE constraint on multiple columns by using ALTER TABLE statement use the following SQL syntax:

In MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Student
ADD CONSTRAINT Unique_Student UNIQUE (Rollno,LastName);

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

In MySQL:
ALTER TABLE Student
DROP INDEX Unique_Student;

In SQL Server / Oracle / MS Access:
ALTER TABLE Student
DROP CONSTRAINT Unique_Student; 

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain UNIQUE values, and cannot contain NULL values i.e it is combination of UNIQUE and NOT NULL constraints.

A table can have only one primary key, which may consist of single or multiple fields or columns.

Defining A SQL PRIMARY KEY on CREATE TABLE

The following example creates a PRIMARY KEY on the "Rollno" column when the "Student" table is created:

In MySQL
CREATE TABLE Persons (
    Rollno int NOT NULL,
    FirstName varchar(255),
    LastName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

In SQL Server / Oracle / MS Access
CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

To name and to define a PRIMARY KEY constraint on multiple columns we use syntax of following example:

In MySQL / SQL Server / Oracle / MS Access
CREATE TABLE Persons (
    Rollno int NOT NULL,
    FirstName varchar(30),
    LastName varchar(30) NOT NULL,
    class varhcar(20),
    Age int,
    CONSTRAINT PK_Student PRIMARY KEY (Rollno,LastName)
);

Note: In the example above there is only ONE PRIMARY KEY (PK_Student). However, the VALUE of the primary key is made up of TWO COLUMNS (Rollno + LastName).

Defining SQL PRIMARY KEY on ALTER TABLE

To specify a PRIMARY KEY constraint on the "Rollno" column when the table is already created, use the following SQL:

In MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Student
ADD PRIMARY KEY (Rollno);

To define a PRIMARY KEY on multiple columns using ALTER TABLE statement:

In MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Student
ADD CONSTRAINT PK_Student PRIMARY KEY (Rollno,LastName);

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

In MySQL:
ALTER TABLE Student
DROP PRIMARY KEY;

In SQL Server / Oracle / MS Access:
ALTER TABLE Student
DROP CONSTRAINT PK_Student; 

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in some other table.

A table which contains a candidate key is called a parent table and table containing the FOREIGN KEY is called as child table.

Example

Consider "Department" table:

DeptNo  DeptName
1       Sports
2       Cultural Programs
3       Biology 
4       Science

Consider "Employee" table:

EmployeeID  LastName  FirstName  Age  DeptNo
1           Dave      Sameer     30     4
2           Tori      Sunaina    23     3
3           Cena      John       20     1
Notice that the "DeptNo" column in the "Employee" table points to the "DeptNo" column in the "Department" table (i.e the values corresponds to DeptNo in Department).

The "DeptNo" column in the "Department" table is the PRIMARY KEY of the "Department" table.

The "DeptNo" column in the "Employee" table is a FOREIGN KEY in the "Employee" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

Defininng SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "DeptNo" column when the "Employee" table is created:

In MySQL:
CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    LastName varchar(50) NOT NULL,
    FirstName varchar(20) NOT NULL,
    Age int,
    DeptNo int,
    PRIMARY KEY (EmployeeID),
    FOREIGN KEY (DeptNo) REFERENCES Department(DeptNo)
);

In SQL Server / Oracle / MS Access:
CREATE TABLE Employee(
    EmployeeID int NOT NULL PRIMARY KEY,
    LastName varchar(50) NOT NULL,
    FirstName varchar(20) NOT NULL,
    Age int,
    DeptNo int FOREIGN KEY REFERENCES Department(DeptNo)
);

To name and to define a FOREIGN KEY constarint on multiple columns use following syntax:

In MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Employee(
    EmployeeID int NOT NULL PRIMARY KEY,
    LastName varchar(50) NOT NULL,
    FirstName varchar(20) NOT NULL,
    Age int,
    DeptNo int,
    PRIMARY KEY (EmployeeID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (DeptNo)
    REFERENCES Department(DeptNo)
);

Defining SQL FOREIGN KEY Using ALTER TABLE

To create a FOREIGN KEY constraint on "DeptNo" column in "Employee" table when "Employee" table is already created, use the following syntax:

In MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Employee
ADD FOREIGN KEY (DeptNo) REFERENCES Department(DeptNo);
To name and to define a FOREIGN KEY on multiple coulmns using ALTER TABLE, use following syntax:

In MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Employee
ADD CONSTRAINT FK_EmployeeDept
FOREIGN KEY (DeptNo) REFERENCES Department(DeptNo);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

In MySQL:
ALTER TABLEEmployee
DROP FOREIGN KEY FK_EmployeeDept;
In SQL Server / Oracle / MS Access:
ALTER TABLE Employee
DROP CONSTRAINT FK_EmployeeDept;

SQL CHECK Constraint

The CHECK constaint is used to limit the values that can be inserted into the table.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Defining SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the "Employee" table is created. The CHECK constraint ensures that you can not have any Employee below 18 years:

In MySQL:
CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);
In SQL Server / Oracle / MS Access:
CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);
To name and to define a CHECK constraint on multiple columns use the following syntax:

In MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sanandreas')
);

Defining SQL CHECK Using ALTER TABLE

To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL Syntax:

In MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Employee
ADD CHECK (Age>=18);
To name and to define a CHECK constraint on multiple colums use the following syntax:

In MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Employee
ADD CONSTRAINT CHK_EmployeeAge CHECK (Age>=18 AND City='Sanandreas');

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

In SQL Server / Oracle / MS Access:
ALTER TABLE Employee
DROP CONSTRAINT CHK_EmployeeAge;
In MySQL:
ALTER TABLE Employee
DROP CHECK CHK_EmployeeAge;

SQL DEFAULT Constraint

The DEFAULT constraint is used to spccify a default value for a column.

The default value will be added to all new records if no other value is specified.

Defining SQL DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the "City" column when the "Employee" table is created:

In My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    LastName varchar(50) NOT NULL,
    FirstName varchar(50),
    Age int,
    City varchar(50) DEFAULT 'Sanandreas'
);
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Employee(
    EmployeeID int NOT NULL,
    LastName varchar(50) NOT NULL,
    FirstName varchar(50),
    Age int,
    JoinDate date DEFAULT GETDATE()
);

Defining SQL DEFAULT Using ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

In MySQL:
ALTER TABLE Employee
ALTER City SET DEFAULT 'Sandnes';
In SQL Server / MS Access:
ALTER TABLE Employee
ALTER COLUMN City SET DEFAULT 'Sanandreas';
In Oracle:
ALTER TABLE Employee
MODIFY City DEFAULT 'Sanandreas';

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

In MySQL:
ALTER TABLE Employee
ALTER City DROP DEFAULT;
In SQL Server / Oracle / MS Access:
ALTER TABLE Employee
ALTER COLUMN City DROP DEFAULT;