Microsoft Business Intelligence

Saturday, 13 July 2019

Cascade delete referential integrity in sql server

We have two tables ProductCategory and product. In the ProductCategory we have store the product category like bikes, Clothing, Accessories, etc. and in the product table, we have product details with product category id.

Product category id is the primary key in the ProductCategory and it is used as a foreign key in the product table.

See the below table script.

create table ProductCategory

(productCategoryID int identity(1,1) PRIMARY KEY,

productCategoryName varchar(100))

create table product

(productID int identity(1,1) PRIMARY KEY,

productName varchar(100),

productcategoryID int CONSTRAINT FK_productCategoryID_Products FOREIGN KEY(productCategoryID)

REFERENCES productCategory(productCategoryID))

Both table have been created. Now I am inserting records in both table

ProductCategory table

insert into productCategory values ('Bikes'),('Components'),('Clothing'),('Accessories')

Now inserting records into the product table

insert into product values

('Mountain-100 Silver, 38',1),('Mountain-100 Silver, 38',1),

('Mountain-100 Silver, 38',1),('Mountain-100 Silver, 42',1),

('Mountain-100 Silver, 42',1),('Mountain-100 Silver, 42',1),

('Road-350-W Yellow, 44',2),('Road-350-W Yellow, 44',2),

('Touring-3000 Yellow, 50',3),('Touring-3000 Yellow, 50',3),

('Touring-3000 Yellow, 50',3),('HL Touring Handlebars 2',4),

('HL Touring Handlebars 1',4)

When we are using the foreign key constraint we can’t refer the foreign key id which is not exist in the primary table.

See this example

In the ProductCategory table ProjectCategoryID=10 is not available and I am trying to insert this value in the project table. We will get the below error.

Now as per the business requirement we need to delete the record form the ProductCategory a table whose ID is 4.

Below is the query to delete the record from the table

delete from productCategory where productCategoryID=4

We can’t delete this record because this id is referred on some other table.

For deleting this record, first of all, we need to delete the records from the child table after that we can delete the record from the parent table.

delete from product where productCategoryID=4

delete from productCategory where productCategoryID=4

Now records deleted from both tables.

Records are deleted from both tables.

Think here we have two tables. Suppose we have n numbers of the table, in this case, we need to write the script to delete the child records from the tables first after that we can able to delete the record from the parent table.

To overcome this issue in SQL server introduce Cascade delete. When we are creating the table we need to define the cascade delete.

When we use the Cascade deleting the user can easily delete the parent table records and child records are automatically deleted from the child table.

See the below example.

I am altering the foreign key constraint as below

Altering the table and adding the FK constraint

ALTER TABLE product

ADD CONSTRAINT fk_name

FOREIGN KEY (productcategoryID)

REFERENCES productCategory (productcategoryID)

ON DELETE CASCADE;

Now I am inserting some records in both tables

insert into productCategory values ('Accessories')

insert into product values

('Mountain-100 Silver, 38',5),('Mountain-100 Silver, 38',5)

Records added into both table

Now I am deleting the record form the parent table.

Now we see the records in both table

Records are deleted from both table.

If we don’t want to delete records from the child tables, on delete we need to set the null values.

ALTER TABLE product

ADD CONSTRAINT fk_name

FOREIGN KEY (productcategoryID)

REFERENCES productCategory (productcategoryID)

ON DELETE SET NULL;

See the example

Now I want to delete the parent table records who’s id is 8. In this case, the null value will be set in place of the foreign key in the product table.

delete from productCategory where productCategoryID=8