Constraints in SQL Server

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 can have only one primary key.
  • A primary key should be defined in a CREATE TABLE statement or ALTER TABLE statement.
1. PRIMARY KEY using the CREATE TABLE statement in SQL server

Syntax

CREATE TABLE table_name
(
column_name1 datatype PRIMARY KEY,
column_name2 datatype,
….................
column_namen datatype
)

OR

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
….................
column_namen datatype
CONSTRAINT constraint_name PRIMARY KEY(column1, column2, ....,column_n)
)


Example 1: Create PRIMARY KEY using CREATE TABLE statement on table 'Students'

CREATE TABLE Students
( Stud_id INT NOT NULL PRIMARY KEY,
Last_name VARCHAR(50) ,
First_name VARCHAR(50)
)


In the above example the PRIMARY key is created on column stud_ID field.

Example 2: Create PRIMARY KEY using CREATE TABLE statement for multiple columns.

CREATE TABLE Students
( Stud_id INT,
Last_name VARCHAR(50) ,
First_name VARCHAR(50)
CONSTRAINT students_pk PRIMARY KEY(Last_name, First_name)
)


2. PRIMARY KEY using the ALTER TABLE statement in SQL server

To create a PRIMARY KEY using ALTER TABLE, the Column should not contain any NULL value.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2,...., Column_n)


Example: Create primary key using ALTER TABLE statement.

ALTER TABLE Students
ADD CONSTRAINT students_pk PRIMARY KEY (Last_name, First_name)


3. DROP Primary key using the ALTER TABLE statement in SQL Server

Syntax

ALTER TABLE table_name
DROP CONSTRAINT constraint_name


Example: Drop primary key on column Stud_id in table 'Students'.

ALTER TABLE Students
DROP CONSTRAINT students_pk


4. Disable a Primary key in SQL server

Syntax

ALTER INDEX constraint_name ON table_name
DISABLE


Example

ALTER INDEX last_name_pk ON Students
DISABLE


5. Enable a Primary key in SQL server

Syntax

ALTER INDEX constraint_name ON table_name
REBUILD


Example

ALTER INDEX last_name_pk ON Students
REBUILD

SQL FOREIGN KEY

  • In relational databases, a foreign key in one table denotes a primary key in another table.
  • The referenced table (contains primary key) is called as parent table and the table containing foreign key is called as child table.
  • A foreign key can be defined either using CREATE TABLE statement or ALTER TABLE statement.
1. Create FOREIGN KEY using the CREATE TABLE statement

Syntax

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
….........
column_name_n datatype
CONSTRAINT fk_name
FOREIGN KEY (column1, column2, …., column_n)
REFERENCES parent_table_name (column1, column2,..., column_n)
)


Now, let us do this with the help of an example.

In the following illustration parent table is titled 'Items' and child table is titled 'Store'. The parent table has a primary key that consists of field 'G_Id'.

Step 1: Create PRIMARY KEY

CREATE TABLE Items
(
Item_Id int PRIMARY KEY,
Item_Name varchar(30),
Category varchar(20)
)

Step 2: Create FOREIGN KEY

CREATE TABLE Inventory
(
Inventory_id INT PRIMARY KEY,
Item_id INT NOT NULL,
quantity INT,
CONSTRAINT fk_inv_Items_id
FOREIGN KEY (Item_id)
REFERENCES Items(Item_id)
)


2. Create a FOREIGN KEY Using ALTER TABLE statement

Syntax

ALTER TABLE table_name
ADD CONSTRAINT fk_name
FOREIGN KEY(column1, column2, ….., column_n)
REFERENCES table_name ( column1, column2, ….., column_n)


Example: Create a  FOREIGN KEY using ALTER TABLE statement

Example

ALTER TABLE Inventory
ADD CONSTRAINT fk_inv_product
FOREIGN KEY (Item_id)
REFERENCES Items(Item_id)


3. FOREIGN KEY With CASCADE DELETE

If a record in the parent table is deleted, then the corresponding records in child table will be deleted automatically. This mechanism is called CASCADE DELETE in SQL server.

Syntax

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
….........
column_namen datatype
CONSTRAINT fk_name
FOREIGN KEY (column1, column2, …., column_n)
REFERENCES parent_table_name (column1, column2,..., column_n)
ON DELETE CASCADE
)


Example 1: Create a FOREIGN KEY with CASCADE using CREATE Table Statement

In the following illustration parent table is titled 'Items' and child table titled 'Store'. The parent table has a primary key that consist of a field 'Item_Id'.

Example

Step 1: Create PRIMARY KEY

CREATE TABLE Items
(
Item_Id int PRIMARY KEY,
Item_Name varchar(30),
Category varchar(20)
)

Step 2: Create FOREIGN KEY

CREATE TABLE Inventory
(
Inventory_id INT PRIMARY KEY,
Item_id INT NOT NULL,
quantity INT,
CONSTRAINT fk_inv_Items_id
FOREIGN KEY (Item_id)
REFERENCES Items(Item_id)
ON DELETE CASCADE
)


Example 2: Create a FOREIGN KEY with CASCADE using ALTER Table Statement

Consider a table titled 'Inventory' as the child table. The Query should be written as:

Example

ALTER TABLE Inventory
ADD CONSTRAINT fk_inv_product
FOREIGN KEY (Item_id)
REFERENCES Items(Item_id)
ON DELETE CASCADE


4. Foreign key with set null on delete

If a record in the parent table is deleted, then the corresponding records in child table will have the foreign key fields set to NULL and hence the records in the child will not be deleted by using ON DELETE SET NULL  keyword.

Syntax

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
….........
column_namen datatype,
CONSTRAINT fk_name
FOREIGN KEY (column1, column2, …., column_n)
REFERENCES parent_table_name (column1, column2,..., column_n)
ON DELETE SET NULL
)


Example 1: Create a FOREIGN KEY with set null on delete using CREATE Table Statement

In the following illustration parent table is titled 'Items' and child table titled 'Store'. The parent table has a primary key that consists of a field 'Item_Id'.

Example

Step 1: Create PRIMARY KEY

CREATE TABLE Items
(
Item_Id int PRIMARY KEY,
Item_Name varchar(30),
Category varchar(20)
)

Step 2: Create FOREIGN KEY

CREATE TABLE Inventory
(
Inventory_id INT PRIMARY KEY,
Item_id INT NOT NULL,
quantity INT,
CONSTRAINT fk_inv_Items_id
FOREIGN KEY (Item_id)
REFERENCES Items(Item_id)
ON DELETE SET NULL
)


Example 2: Create a FOREIGN KEY with set null on delete using ALTER Table Statement in SQL server.

Example

ALTER TABLE Inventory
ADD CONSTRAINT fk_inv_Items
FOREIGN KEY (Item_id)
REFERENCES Items(Item_id)
ON DELETE SET NULL


5. DROP a foreign key in SQL server

Syntax

ALTER TABLE table_name
DROP CONSTRAINT fk_name


6. Enable a foreign key in SQL server

Syntax

ALTER TABLE table_name
CHECK CONSTRAINT fk_name


7. Disable a foreign key in SQL server

Syntax

ALTER TABLE table_name
NOCHECK CONSTRAINT fk_name

SQL Server Check Constraints

A check constraints in SQL server allows the user to specify a condition on each row in a table.

1. Check constraint using a CREATE TABLE statement

Syntax

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
….................
column_namen datatype,
CONSTRAINT constraint_name
CHECK [NOT FOR REPLICATION] (column_name condition)
)


Example

Check constraint using CREATE TABLE statement in SQL server on table titled 'Students'
CREATE TABLE Students
( Stud_id INT NOT NULL,
Last_name VARCHAR(50) NOT NULL,
First_name VARCHAR(50),
CONSTRAINT check_Stud_id
CHECK (Stud_id BETWEEN 1 and 100)
)


In above example a check constraint is created on check_Stud_id. This  constraint ensures that the Stud_id field contains values between 1
and 100.

2. Check constraint using CREATE TABLE statement

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (column_name condition)


Example: Check constraint using ALTER TABLE statement on table titled 'Students'.

Example

ALTER TABLE Students
ADD CONSTRAINT Check_First_name
CHECK (First_name IN ('SAM', 'JON'))


In above example the check constraint is created on existing table titled 'Students' as Check_First_name. It ensures that the First_name field only contain values such as SAM or JON.  

3. Check constraint using DROP TABLE statement

Syntax

ALTER TABLE table_name
DROP CONSTRAINT constraint name


Example

Check constraint using DROP TABLE statement in SQL server on table titled 'Students'.
ALTER TABLE Students
DROP CONSTRAINT First_name

▲