Cascading SQL Server 2005 Uninstall

Question

I am 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

User_ID Messages

I basically have a ton of tables that reference User_ID from the user. I would like to set up cascading deletion in a single table so that I can delete the User object and make sure that all the tables that reference the user are deleted.

However, I understand that I need to set a delete action for each table that references the user. that is, I need to set cascading delete on each child table. Do I understand correctly?

Cascading SQL Server

Update: It looks like I have to set it for each relationship. Where should I think of these relationships as being "saved"? Perhaps my concept is wrong.

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 link, you can indicate what action to take.

The easiest way to handle this is to use SQL Server Management Studio. Create a parent table and find all PK-FK relationships.

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

• No Action - this will result in an FK error when this happens.
• Cascade - delete a child entry
• Set to null - the value of the FK column will be null. This may cause an error when null values ​​are not allowed in the child table.
• Set default value - if the FK column in the child table has a default value, this will be the new value in the child column.

Answer 2

What you describe seems to be bad mojo (especially if you don't use referential integrity). Consider the following:

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

If you understood correctly that you are saying that when you delete a client record that refers to a user record, you also want to delete this user record (right?).

If you do this, and there are Employee and Guest entries that reference the same user entry, they will suddenly point to nothing (unless you use 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 deletions ...