A constraint is a condition or check which is applicable on a field or set of fields.

Integrity constraints are the protocols that are table's data columns must follow

Column Constraints

---

Column constraints are conditions on the data that can be inserted into a given column (i.e. they are applicable only to individual columns).

1. Unique Constraint : It ensures that two rows can't have same values in the specified column. You can have many UNIQUE constraints per table,

The following SQL query creates UNIQUE constraint on the "EmpID" column when the "Employee" table is created:

 

create table Employee (
    EmpID int not null unique,
    EmpName varchar(20) not null,
    Age int not null,
    Gender varchar(10) not null,
    Salary decimal(9,1) not null
);
         
                         

copy

2. Primary Key Constraint : The PRIMARY KEY constraint uniquely identifies each record in a table. It must contain unique values, and cannot contain NULL values. A table can have only ONE primary key and this primary key can consist of single or multiple columns (fields) as combined.

The following SQL query creates PRIMARY KEY on the "EmpID" column when the "Employee" table is created:

 

create table Employee (
    EmpID int not null primary key,
    EmpName varchar(20) not null,
    Age int not null,
    Gender varchar(10) not null,
    Salary decimal(9,1) not null
);
         
                         

copy

3. Default Constraint : If user does not enter any value,this constraint automatically inserts the default value
Lets us understand by an example

Write a SQL statement to create a simple table emp which includes ecode,name,grade,gender

    CREATE TABLE emp
    (
    ecode integer not null primary key,
    name varchar(20) not null,
    grade varchar(2) DEFAULT 'E1'
    gender varchar(1) not null
    );
                        

copy

4. Check Constraint : This constraint limits the value that will be inserted into a column.
If you define a CHECK constraint on a column it will allow only certain value in a column
Lets us understand by an example

Write a SQL statement to create a simple table emp which includes ecode,name,gross where gross inputted should be greater than 2000

    CREATE TABLE emp
    (
    ecode integer not null primary key,
    name varchar(20) not null,
    gross int not null check(gross>2000)
    );
                        

copy

We can also apply check constraints in more than one column,this will be applied after all the column have been defined
Lets us understand by an example

Write a SQL statement to create a simple table emp which includes ecode,name,salary,gender where gender should be 'F' and Salary should be greater than 2000

    CREATE TABLE emp(
    ecode integer not null primary key,
    name varchar(20) not null,
    salary int not null,
    gender varchar(2) not null
    CHECK (gender='F' AND salary>2000)
    );
                        

copy

5. Foreign Key Constraint : This constraint is used to maintain relationship between two or more records in a table,and user cannot able to accidently delete or update the related data.

When two tables are related by a common column (or set of column),then one table with the primary key is referred to as a parent table and table with the foreign key is referred to as child table.

Lets us understand by an example

Write an SQL statement to apply "primary key" on the customer table (parent table) and "Foreign Key" on the sales table (child table)

create table Customer
( CustomerId int not null primary key,
Name varchar(20) not null,
Location varchar(10) not null
);
                
    
                        

copy

create table Sales
( SalesId int not null,
CustomerId int not null references customer(id),
Amount int not null
);
    
              
    
                        

copy

Table Constraints

---

When a constraint is to be applied on a group of column of the table it is called as table constraints (i.e. table level constraints apply to the whole table.)

1. CHECK as a table constraint : 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.

 

create table Employee(
    EmpID int not null,
    EmpName varchar(20) not null,
    Age int not null,
    Gender varchar(10) not null,
    Salary decimal(9,1) not null,
    check (Age > 18 and Salary > 10000)
);
         
                         

copy

2. UNIQUE as a table constraint : To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL query

 

create table Employee (
    EmpID int not null,
    EmpName varchar(20) not null,
    Age int not null,
    Gender varchar(10) not null,
    Salary decimal(9,1) not null,
    unique (EmpId, EmpName)
);
         
                         

copy

3. PRIMARY KEY as a table constraint : To name a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL query

 

create table Employee (
    EmpID int not null,
    EmpName varchar(20) not null,
    Age int not null,
    Gender varchar(10) not null,
    Salary decimal(9,1) not null,
    primary key (EmpId, EmpName)
);
         
                         

copy