CASCADE IT (UPDATE / DELETE)

Many a time we need to clean up all the table data. For this we have first traverse through each child table and delete data gradually from bottom to top.

This is time consuming, instead if we alter table to add ON DELETE CASCADE clause to the reference key field then by deleting master table record the corresponding child record will get deleted.

ALTER TABLE [dbo].[CHILD_TAB] ADD  CONSTRAINT [FK_CHILD_TAB_PARENT_TAB] FOREIGN KEY([first_id])

REFERENCES [dbo].[PARENT_TAB] ([first_id])

ON UPDATE CASCADE

ON DELETE CASCADE

Same way if we want modifying master record modify the related child record then we can alter table to have ON UPDATE CLAUSE to reference key field.

EXAMPLE: Here in this example code we are having Child table as ‘CHILD_TAB’ and Parent table as ‘PARENT_TAB’ and we are making first_id of ‘CHILD_TAB’  as foreign key field to the Primary Key field first_id of ‘PARENT_TAB’ with ON UPDATE and ON DELETE CASCADE.

update parent_tab

set first_id = 1 where first_id=5

Now when we update first_id of ‘PARENT_TAB’ automatically all the child record will get updated.

delete parent_tab where first_id=1

Similirly when we delete ‘PARENT_TAB’ where first_id=1, it automatically delete all record in child table ‘CHILD_TAB’ with first_id=1

Post Reference: Vikram Aristocratic Elfin Share