ON DELETE CASCADE and ON UPDATE CASCADE in SQL Server

We can use these statements together with the Foreing Key constraints. First of all, it is necessary to understand in detail what Foreign Key is. You can benefit from the article “What is Primary Key and Foreign Key“.

If you have defined Foreing key as I have described in the article “What is Primary Key and Foreign Key“, you cannot normally delete the record in the primary table without deleting the record in the table with the foreing key. When you try to delete it, you receive the error as follows.

Msg 547, Level 16, State 0, Line 4

The DELETE statement conflicted with the REFERENCE constraint “FK_xx”. The conflict occurred in database “xx”, table “dbo.xx”, column ‘xx’.

The statement has been terminated.

What is ON DELETE CASCADE

If you created the Foreign Key by specifying “ON DELETE CASCADE”, when you delete the record in the primary table, it automatically deletes the records in the child table that the foreign key is defined in. You wouldn’t get the above error. Of course, this is not always desirable. Thats why this is not the default behavior.

Create Foreing Key With ON DELETE CASCADE

You can create a foreign key by specifying “ON DELETE CASCADE” with the script below.

What is ON UPDATE CASCADE

If you specify “ON UPDATE CASCADE” when creating Foreign Key, this ensures that when there is an update in the column in the primary table, this update is reflected in the table with the foreign key. Or if you want to change the column type in the primary table, this change is also reflected in the table with the foreign key.

If you did not create the Foreign Key with the ON UPDATE CASCADE, you will get an error like the following when you try to update the column referenced by foreing key in the primary table.

Msg 547, Level 16, State 0, Line 4

The UPDATE statement conflicted with the REFERENCE constraint “FK_xx”. The conflict occurred in database “xx”, table “dbo.xx”, column ‘xx’.

The statement has been terminated.

Create Foreing Key With ON UPDATE CASCADE

You can create a foreign key by specifying “ON UPDATE CASCADE” with the script below.