Skip to content

Constraints:

SQL constraints are used to specify rules for data in a table

Types of constraints:

1.Primary key :

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

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

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

1
2
3
4
5
6
CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

1
2
3
4
5
6
7
8
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
);

How to add primary key constraint for existing table

``` Alter table Persons add constrait pk_Persons_id primary key(id) );

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
## 2.Unique key :
The UNIQUE constraint ensures that all values in a column are different.

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.

Both UNIQUE and PRIMARY KEY are similar but PRIMARY KEY does not allow null values where as unique allows only one null value.

**UNIQUE constraint on the "ID" column when the "Persons" table is created:

CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );

1
**UNIQUE constraint on multiple columns when the "Persons" table is created:

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName) );

1
2
3
4
5
6
7
 How to add UNIQUE KEY constraint for existing table

 ```
Alter table Persons
add constrait uq_Persons_id
Unique(id)
);

NOT NULL:

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

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

***The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:

1
2
3
4
5
6
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

4.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 column it will allow 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.

***CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that the age of a person must be 18, or older:

1
2
3
4
5
6
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

How to add CHECK constraint on existing table

1
2
3
alter table Persons
add constraint ck_persons_age
check (age>=18)

5. DEFAULT constraint

The DEFAULT constraint is used to set a default value for a column.

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

***The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:

1
2
3
4
5
6
7
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

How to add DEFAULT constraint on existing table

1
2
3
alter table Persons
add constraint DF_persons_City
DEFAULT 'Sandnes' for city

5.FAREIGN KEY constraint:

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

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
Look at the following two tables:

Persons Table
PersonID    LastName    FirstName   Age
1   Hansen      Ola     30
2   Svendson    Tove    23
3   Pettersen   Kari    20

Orders Table
OrderID OrderNumber PersonID
1   77895   3
2   44678   3
3   22456   2
4   24562   1
Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.

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

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

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

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

1
2
3
4
5
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

***To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

1
2
3
4
5
6
7
8
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

***How to create FAREIGN KEY on existing table

1
2
3
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);