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 .
    • When this constraint is applied to a column, it must contain a value for every row in the table.

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));

SQL constraints are a crucial aspect of database design and development. They provide a way to enforce data integrity and consistency, and ensure that data is entered correctly and consistently across all applications. By using constraints effectively, developers can prevent errors and inconsistencies, and create robust and reliable databases that are essential for modern applications.

Recommended Articles:

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