SQL Constraints | Types and Examples

1) NOT NULL Constraint

• • • It ensures column cannot store NULL value
    • If column is specified as NOT NULL then ,null value can not be stored in this particular column .

Example – 

CREATE TABLE Employee(
EmployeeID                  Number(6)     NOT NULL,
Employee_FirstName        VarChar2(30)  NOT NULL,
Employee_LastName         VarChar2(20),
)

2) UNIQUE Constraint

• • • Make sure that all the values in a column are unique
    • UNIQUE constraint can be used either on multiple columns or on a single column with
    • PRIMARY KEY constraint automatically has a UNIQUE constraint, to guarantee the uniqueness of a column.
    • A table can have many UNIQUE constraints but can have a single primary key constraint.

Example:

CREATE TABLE Employee(
EmployeeID                 Number(6) NOT NULL UNIQUE,
Employee_FirstName         VarChar2(30)  NOT NULL,
Employee_LastName          VarChar2(20)
);

3) PRIMARY KEY Constraint

• • • A combination of a NOT NULL and UNIQUE.
    • No two rows in a table can have the same primary key value.
    • NULL value cannot be entered in a primary key column.

Example:

CREATE TABLE Employee(
EmployeeID                      Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
Employee_FirstName         VarChar2(30)  NOT NULL,
Employee_LastName          VarChar2(20),
)

4) FOREIGN KEY Constraint

• • • Ensure the referential integrity of the data in one table to match values in another table
    • Creates a kind of link between the tables.
    • It is also called as REFERENCE KEY.
    • A table may have more than one FOREIGN KEY.
    • FOREIGN KEY should not contain NULL values.

The following statement establishes a foreign key on the Dept_id column of the Employee table that references the Dept_id column of the Departments table.

CREATE TABLE Employee (
EmployeeID                Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
Employee_FirstName        VarChar2(30)  NOT NULL,
Employee_LastName         VarChar2(20),
Dept_id                   INT       CONSTRAINT Fkey1 REFERENCES Departments(dept_id)
);

5) CHECK  Constraint

• • • Ensures that the value in a column meets a specific condition
    • If the value does not match the condition, then it will not be inserted into a table

Check constraint on create table

Write a query to create a table Employee, with columns EmployeeID, Employee_FirstName, Employee_LastName and City and The city must be Bangalore.

CREATE TABLE Employee ( 
EmployeeID              Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
Employee_FirstName      VarChar2(30)  NOT NULL,
Employee_LastName       VarChar2(20), 
City                    VarChar2(255)   CHECK (City==’Bangalore’) );

CHECK constraint on multiple columns

Write a query to create a table Employee, with columns EmployeeID, Employee_FirstName, Employee_LastName and City .The City must be Mumbai, and the age of Employee  >20.

CREATE TABLE Employee ( 
EmployeeID                Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
Employee_FirstName        VarChar2(30)  NOT NULL,
Employee_LastName         VarChar2(20), 
Age                       int, 
City                      VarChar2(255)    CONSTRAINT chk  CHECK (City==’Mumbai’ AND Age>20));

Recommended Articles:

• • SQL Special Operators
  • SQL Sorting (Order By)
  • SQL Group By
  • SQL Aggregate Function
  • SQL Having