SQL Server 2005 Cascading Delete

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 under开发者_开发百科standing 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.

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.

What you are describing seems like bad mojo (especially if you don't enforce referential integrity). Consider the following:

You have 3 tables that reference the user table: Client, Employee and Guest

If I understand you correctly you are saying that when you delete a Client record that references a User record then you want that User record deleted as well (correct?).

If you do this and there are Employee and Guest records that reference that same user record then they will suddenly be pointing to nothing (if you don't enforce referential integrity).

It seems to me that if you have a bunch of tables that reference the User table then you should not do cascading deletes...