SQL Constraints

Introduction

SQL constrains are used to define rules for the data in table. It can be define, inside the table when table is created or after creating table.

SQL Constraints are listed below.

NoConstraint NameDescription
1NOT NULLIt ensures that a column cannot accept NULL values.
2UNIQUEIt ensures that each row and column have a unique value.
3CHECKIt ensures that the values in a column satisfies the condition.
4DEFAULTDefines a default value for a column.
5PRIMARY KEYIt is a combination of a NOT NULL and UNIQUE.
6FOREIGN KEYIt is used to connect two tables together.

1. SQL NOT NULL

The SQL NOT NULL constraint ensures that, a column should not accept NULL values.

Example : Query using NOT NULL constraint.
Write a query to create a table by using NOT NULL constraint.

CREATE TABLE Employee
(
    Emp_ID int NOT NULL,
    Emp_Name varchar (255) NOT NULL,
    Emp_Address varchar (255),
    Emp_City Varchar (255)
);

2. SQL UNIQUE Constraints

The UNIQUE constraint is used to ensure that each row and column have a unique value.

Example : Query using UNIQUE constraint.
Create a table 'Employee' by using UNIQUE constraints.

CREATE TABLE Employee
(
    Emp_ID int UNIQUE,
    Emp_Name varchar (255) NOT NULL,
    Emp_Address varchar (255),
    Emp_City Varchar (255),
);

3. PRIMARY KEY

  • Primary key uniquely identifies each row in the table.
  • When multiple columns are used as a primary key, it is known as composite primary key.
  • A primary key should not have null value. Each table have only one primary key.
Example : Query using PRIMARY KEY constraint.
Create a table 'Students' using PRIMARY KEY constraints.

i) My SQL

CREATE TABLE Students
(
    S_ID int not null,
    Name Varchar (255) not null,
    Address Varchar (255),
    City varchar (255),
    PRIMARY KEY (S_ID)
);

ii) Oracle/ SQL Server

CREATE TABLE Students
(
    S_ID int not null PRIMARY KEY,
    Name Varchar (255) not null,
    Address Varchar (255),
    City varchar (255)
);

Example : Write a query to drop a primary key.

i) My SQL Statement.
ALTER TABLE Students
DROP PRIMARY KEY;

ii) Oracle Statement.
ALTER TABLE Students
DROP CONSTRAINT Pk_S_ID;

4. SQL FOREIGN KEY

In relational databases, a foreign key in one table denotes a primary key in another column.

Example : Query using FOREIGN KEY constraint.
Consider the following two tables, one is entitled 'Students' and another is 'Examination'.

Table1 : 'Students'

Stud_IDStud_NameCityCountry
1MarkLondonEngland
2AlexParisFrance
3BobSydneyAustralia
4JayaDelhiIndia
5SurendraBarodaIndia

Table2 : 'Examination'

Exam_NoStud_IDResult
S1011Pass
S1022Fail
S1033Pass
S1044Pass
S1055Pass

  • The 'Stud_ID' column in the table 'Students' is a Primary key in the 'Students' table.
  • The 'Stud_ID' column in the table 'Examination' is a foreign key in the 'Examination' table.

FOREIGN KEY constraints on CREATE TABLE

To create a foreign key on 'Stud_ID' column while creating a 'Examination' table.

i) My SQL

CREATE TABLE Examination
(
    Exam_No varchar(255) NOT NULL,
    Result varchar(255) NOT NULL,
    Stud_ID int,
    PRIMARY KEY(Exam_No),
    FOREIGN KEY(Stud_ID) REFERENCES Students(Stud_ID)
);

ii) Oracle

CREATE TABLE Examination
(
    Exam_No varchar(255) PRIMARY KEY,
    Result varchar(255) NOT NULL,
    Stud_ID int FOREIGN KEY REFERENCES Students(Stud_ID)
);