SQL Server 2005 Cascading Delete

Question

I'm not 100% sure how cascading deletes work.

I have for simplicity tables that look like this

User User_ID

ExtendedUser User_ID

Comments User_Id

Posts User_ID

I basically have a ton of tables which reference the User_ID from User. I'd like to set a cascading delete on one table so that I can delete the User object and ensure that all tables that reference User are deleted.

However, my understanding is that I need to set the delete action on every table that references User. that is I need to set the "cascade delete" on every child table. Is my understanding correct?

SQL Server Cascading

Update: It looks like I have to set it for every relationship. Where should I think of these relationships as being "stored"? Maybe my conception is not right.

It looks like I can set all the referential integrity rules for each relationship using the management studio from the parent table.

Answer 1

For each relationship, you can specify what action to take.

Easiest way to manage this likely would be to use SQL Server Management Studio. Design your parent table, and find all the PK-FK relationships.

For each, choose which path to take when a Delete event occurs:

• No Action - this would cause a FK error when it occurs
• Cascade - delete the child record
• Set null - the FK column value would be null'd. This would throw an err obviously when nulls aren't allowed in the child table.
• Set default - if the FK column on the child table has a default, it would then be the new value in the child column.