CODEWITHSUNDEEP
sql-serverforeign-key-relationship
user331905
user331905

Reputation: 317

SQL Server 2008 on Delete Cascade

When foreign keys in a table have ON DELETE CASCADE set to them, does this mean that "deletion is allowed" or does it mean that SQL Server will actually perform all of the associated deletes when a record is deleted?

In other words, if I have a set of tables linked together with foreign keys with ON DELETE CASCADE, do I have to go to each individual table and explicitly perform the delete function or will SQL Server do it for me?

Upvotes: 1

Views: 514

Answers (3)

Bruno Franco
Bruno Franco

Reputation: 2037

It means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All childs of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) and there is ON DELETE CASCADE defined, these should be killed, too (and all descendants, as long as there is a cascade effect defined).

Took it from: https://dba.stackexchange.com/questions/44956/good-explanation-of-cascade-on-delete-update-behavior

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

It means that if you delete a parent in a FK relationship, SQL Server will automatically delete all the children.

Upvotes: 1

Alex
Alex

Reputation: 371

Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

Took that from IBM site. I guess it means yes! :)

Upvotes: 2

Related Questions