5

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.

Community
  • 1
  • 1
Curtis White
  • 6,213
  • 12
  • 59
  • 83

2 Answers2

9

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.

enter image description here

p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • Campbell Thanks. This is what I did! I was thinking I would have to check each child table but I can see the relationships from the parent. I think that was my confusion. – Curtis White Feb 14 '11 at 16:44
0

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...

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • I'm pretty sure Curtis meant it the other way around -- that if you delete a parent record, the children are deleted, not vice versa. I have definitely had situations where it was nice that the database supported that. But in generally, I agree that you should think very hard about whether to enable cascade deletes. It's safer to make your deletes explicit in your code -- then you have to think about it, and are less likely to suffer an unfortunate data loss accident. – Katie Kilian Jan 05 '12 at 19:37