SQL Constraints | SQL for Beginners - Software Development PDF Download

SQL Constraints

Constraints are used to limit the type of data that can go into a table and implement restriction on data to be inserted in the table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

We will focus on the following constraints:

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK

• DEFAULT

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE Persons
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 )

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

Column Level

CREATE TABLE Persons
 (
 P_Id int NOT NULL UNIQUE,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 )

Table Level

CREATE TABLE Persons
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255),
 CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
 )

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
 ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

To DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons
 DROP CONSTRAINT uc_PersonID

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

SQL PRIMARY KEY Constraint on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:

CREATE TABLE Persons
 (
 P_Id int NOT NULL PRIMARY KEY,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 )

Table Level:

CREATE TABLE Persons
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255),
 CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
 )

SQL PRIMARY KEY Constraint on ALTER TABLE

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
 ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)

To DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons
 DROP CONSTRAINT pk_PersonID

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The "Persons" table:

P_Id    LastName       FirstName     Address                City

1         Hansen           Ola              Timoteivn 10          Sandnes

2        Svendson       Tove             Borgvn 23             Sandnes

3        Pettersen         Kari            Storgt 20               Stavanger

The "Orders" table:

O_Id     OrderNo         P_Id

1           77895            3

2          44678             3

3          22456             2

4          24562             1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:

CREATE TABLE Orders
 (
 O_Id int NOT NULL PRIMARY KEY,
 OrderNo int NOT NULL,
 P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
 )

Table level:

CREATE TABLE Orders
 (
 O_Id int NOT NULL,
 OrderNo int NOT NULL,
 P_Id int,
 PRIMARY KEY (O_Id),
 CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
 REFERENCES Persons(P_Id)
 )

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:

ALTER TABLE Orders
 ADD CONSTRAINT fk_PerOrders
 FOREIGN KEY (P_Id)
 REFERENCES Persons(P_Id)

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE Orders
 DROP CONSTRAINT fk_PerOrders

SQL 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.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.

CREATE TABLE Persons
 (
 P_Id int NOT NULL CHECK (P_Id>0),
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 )

CREATE TABLE Persons
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255),
 CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
 )

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons
 ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

To DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

ALTER TABLE Persons
 DROP CONSTRAINT chk_Person

SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

CREATE TABLE Persons
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255) DEFAULT 'Sandnes'
 )

CREATE TABLE Orders
 (
 O_Id int NOT NULL,
 OrderNo int NOT NULL,
 P_Id int,
 OrderDate date DEFAULT GETDATE()
 )

SQL DEFAULT Constraint on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

ALTER TABLE Persons
 ALTER COLUMN City SET DEFAULT 'SANDNES'

ALTER TABLE Persons
 ADD CONSTRAINT df_Person Default ‘Sandes’ for City

To DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE Persons
 ALTER COLUMN City DROP DEFAULT

ALTER TABLE Persons
 DROP CONSTRAINT df_Person