SQL Constraints
-Ashu Mehta
Database systems
SCSE
Constraints can be specified when the
table is created with the CREATE
TABLE statement, or after the table is
created with the ALTER TABLE
statement.
SQL constraints are used to specify rules
for the data in a table.
Constraints are used to limit the type of
data that can go into a table. This ensures
the accuracy and reliability of the data in
the table.
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
NOT NULL - Ensures that a column cannot have
a NULL value
UNIQUE - Ensures that all values in a column
are different
PRIMARY KEY - A combination of a NOT
NULL and UNIQUE. Uniquely identifies each
row in a table
FOREIGN KEY - Uniquely identifies a
row/record in another table
CHECK - Ensures that all values in a column
satisfies a specific condition
DEFAULT - Provides a default value for a
column
NOT NULL
By default, a column can hold NULL
values.
The NOT NULL constraint enforces a
column to NOT accept NULL values.
Example:-
CREATE TABLE Employees09(
Emp_ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Deparment varchar(255) NOT NULL,
Acct_No varchar(255) NOT NULL
);
UNIQUE Constraint
The UNIQUE constraint ensures that all
values in a column are different.
Both the UNIQUE and PRIMARY KEY
constraints provide a guarantee for
uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically
has a UNIQUE constraint.
However, you can have many UNIQUE
constraints per table, but only one
PRIMARY KEY constraint per table.
EXAMPLE
CREATE TABLE Person20 (
ID int NOT NULL UNIQUE, LastName
varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Multiple Columns
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int, CONSTRAINT UC_Person UNIQUE (ID)
);
With Alter Command
ALTER TABLE Persons
ADD UNIQUE (ID);
Drop Unique Constraint
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely
identifies each record in a database table.
Primary keys must contain UNIQUE
values, and cannot contain NULL values.
A table can have only one primary key,
which may consist of single or multiple
fields.
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
On Alter Table
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
• Drop Primary Key
ALTER TABLE Persons
DROP PRIMARY KEY;
FOREIGN KEY Constraint
A FOREIGN KEY is a key used to link two
tables together.
A FOREIGN KEY is a field (or collection of
fields) in one table that refers to the PRIMARY
KEY in another table.
The table containing the foreign key is called the
child table, and the table containing the
candidate key is called the referenced or parent
table.
PersonID LastName FirstName Age
1 Hansen Ola 30
2 Svendson Tove 23
3 Pettersen Kari 20
OrderID OrderNumber PersonID
1 77895 3
2 44678 3
3 22456 2
4 24562 1
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFEREN
CES Persons(PersonID)
);
CHECK Constraint
The CHECK constraint is used to limit
the value range that can be placed in a
column.
If you define a CHECK constraint on a
single column it allows only certain
values for this column.
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 Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AN
D City='Sandnes')
);
With Alter
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (
Age>=18 AND City='Sandnes');
Drop
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
DEFAULT Constraint
The DEFAULT constraint is used to
provide a default value for a column.
The default value will be added to all new
records IF no other value is specified.
Example: CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
With
Alter
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
