如何在 SQL Server 中使用级联删除? [英] How do I use cascade delete with SQL Server?

问题描述

我有 2 个表:T1 和 T2，它们是包含数据的现有表.我们在 T1 和 T2 之间有一对多的关系.当 T1 中的记录被删除，T2 中的所有关联记录也被删除时，如何更改表定义以在 SQL Server 中执行级联删除.

I have 2 tables: T1 and T2, they are existing tables with data. We have a one to many relationship between T1 and T2. How do I alter the table definitions to perform cascading delete in SQL Server when a record from T1 is deleted, all associated records in T2 also deleted.

他们之间存在外部约束.我不想删除表或创建触发器来删除 T2.例如，当我删除一个员工时，所有的评论记录也应该消失了.

The foreign constraint is in place between them. I don't want to drop the tables or create a trigger to do the deletion for T2. For example, when I delete an employee, all the review record should be gone, too.

T1 - 员工，

Employee ID      
Name
Status

T2 - 绩效评估，

Employee ID - 2009 Review
Employee ID - 2010 Review

推荐答案

你需要，

• 删除现有的外键约束，
• 在启用 ON DELETE CASCADE 设置的情况下添加一个新的.

• Drop the existing foreign key constraint,
• Add a new one with the ON DELETE CASCADE setting enabled.

类似于:

ALTER TABLE dbo.T2
   DROP CONSTRAINT FK_T1_T2   -- or whatever it's called

ALTER TABLE dbo.T2
   ADD CONSTRAINT FK_T1_T2_Cascade
   FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE

这篇关于如何在 SQL Server 中使用级联删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！