SQL Server 2005 Cascading Delete

存储架构 2017-11-13 阅读原文

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.

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.

Hello, buddy!

责编内容by:Hello, buddy!阅读原文】。感谢您的支持!

您可能感兴趣的

sql server 索引碎片整理 1. 首先需要用sql 查询出数据库索引碎片情况: SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, in...
T-SQL Tuesday #96 – Folks Who Have Made a Differen... This months TSQL Tuesday subject is a shout out and high-five to those peo...
Enhancement Request for SQL Developer for users of... Juergen Schuster , who has been enthusiastically trying OraOpenSource Logger ,...
SQL Performance – Using Query Hint OPTION (FAST N)... It is fun working with SQL/Application developer where you exchange a lot of te...
Eliminate duplicates in the SQL query i have a table with 6 fields. the columns are ID, new_id price,title,Img,Active....