Microsoft Business Intelligence

Saturday, 13 July 2019

Cascade update 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 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 (1,'Bikes'),(2,'Components'),(3,'Clothing'),(4,'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 update the record in the ProductCategory table and set the ProductCategoryID 4 to 400.

Below is the query to update the record in the table

Update productCategory

set productCategoryID=400

where productCategoryID=4

In this case we need to write the script to drop the constraint from the both table and update the records and after update we need to created foreign key constrain.

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

When we use the Cascade update the user can easily update the parent table records and child records are automatically updated.

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 UPDATE CASCADE;