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.
| No | Constraint Name | Description |
|---|
| 1 | NOT NULL | It ensures that a column cannot accept NULL values. |
| 2 | UNIQUE | It ensures that each row and column have a unique value. |
| 3 | CHECK | It ensures that the values in a column satisfies the condition. |
| 4 | DEFAULT | Defines a default value for a column. |
| 5 | PRIMARY KEY | It is a combination of a NOT NULL and UNIQUE. |
| 6 | FOREIGN KEY | It 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_ID | Stud_Name | City | Country |
|---|
| 1 | Mark | London | England |
| 2 | Alex | Paris | France |
| 3 | Bob | Sydney | Australia |
| 4 | Jaya | Delhi | India |
| 5 | Surendra | Baroda | India |
Table2 : 'Examination'
| Exam_No | Stud_ID | Result |
|---|
| S101 | 1 | Pass |
| S102 | 2 | Fail |
| S103 | 3 | Pass |
| S104 | 4 | Pass |
| S105 | 5 | Pass |
- 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)
);
