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), ('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;

                         

     
Now I am updating the record in the parent table.

Update productCategory set productCategoryID=400 where productCategoryID=4

Records added into both table

Before update value in both table

     

Now I am updating the record.

 

                     

Now see the records in both table.

                     

           

Hope this will help when we are designing the tables in the database for the application.