本文翻译自：How do I use cascade delete with SQL Server?

I have 2 tables: T1 and T2, they are existing tables with data. 我有2个表：T1和T2，它们是具有数据的现有表。 We have a one to many relationship between T1 and T2. T1和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. 当删除T1中的一条记录时，如何更改表定义以在SQL Server中执行级联删除，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. 我不想删除表或创建触发器来删除T2。 For example, when I delete an employee, all the review record should be gone, too. 例如，当我删除员工时，所有复查记录也应消失。

T1 - Employee, T1-员工，

Employee ID      
Name
Status

T2 - Performance Reviews, T2-效果评论，

Employee ID - 2009 Review
Employee ID - 2010 Review

---

#1楼

参考：https://stackoom.com/question/QGgq/如何在SQL-Server中使用级联删除

---

#2楼

First To Enable ONCascade property: 首先要启用ONCascade属性：

1.Drop the existing foreign key constraint 1，丢弃现有的外键约束

2.add a new one with the ON DELETE CASCADE setting enabled 2.添加一个新的启用ON DELETE CASCADE设置

Ex: 例如：

IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.Response'))
 BEGIN 

ALTER TABLE [dbo].[Response] DROP CONSTRAINT [FK_Response_Request]  

ALTER TABLE [dbo].[Response] WITH CHECK ADD CONSTRAINT [FK_Response_Request]  FOREIGN KEY([RequestId])
REFERENCES [dbo].[Request] ([RequestId])
ON DELETE CASCADE
END

ELSE

 BEGIN 
 ALTER TABLE [dbo].[Response] WITH CHECK ADD CONSTRAINT [FK_Response_Request]  FOREIGN KEY([RequestId])
REFERENCES [dbo].[Request] ([RequestId])
ON DELETE CASCADE
END

Second To Disable ONCascade property: 第二个禁用ONCascade属性：

1.Drop the existing foreign key constraint 1，丢弃现有的外键约束

2.Add a new one with the ON DELETE NO ACTION setting enabled 2.添加一个新的启用ON DELETE NO ACTION设置

Ex: 例如：

IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.Response'))
 BEGIN 
ALTER TABLE [dbo].[Response] DROP CONSTRAINT [FK_Response_Request]  

ALTER TABLE [dbo].[Response] WITH CHECK ADD CONSTRAINT [FK_Response_Request]  FOREIGN KEY([RequestId])
REFERENCES [dbo].[Request] ([RequestId])
ON DELETE CASCADE
END

ELSE

 BEGIN 
 ALTER TABLE [dbo].[Response] WITH CHECK ADD CONSTRAINT [FK_Response_Request]  FOREIGN KEY([RequestId])
REFERENCES [dbo].[Request] ([RequestId])
ON DELETE NO ACTION 
END

---

#3楼

You can do this with SQL Server Management Studio. 您可以使用SQL Server Management Studio进行此操作。

→ Right click the table design and go to Relationships and choose the foreign key on the left-side pane and in the right-side pane, expand the menu "INSERT and UPDATE specification" and select "Cascade" as Delete Rule. →右键单击表设计，然后转到“关系”，然后在左侧窗格中选择外键，然后在右侧窗格中，展开菜单“ INSERT and UPDATE specification”，然后选择“ Cascade”作为“删除规则”。

---

#4楼

To add "Cascade delete" to an existing foreign key in SQL Server Management Studio: 要将“层叠删除”添加到SQL Server Management Studio中的现有外键，请执行以下操作：

First, select your Foreign Key, and open it's "DROP and Create To.." in a new Query window. 首先，选择您的外键，并在新的查询窗口中将其打开为“ DROP and Create To ..”。

Then, just add ON DELETE CASCADE to the ADD CONSTRAINT command: 然后，只需将ON DELETE CASCADE添加到ADD CONSTRAINT命令：

And hit the "Execute" button to run this query. 并点击“执行”按钮以运行此查询。

By the way, to get a list of your Foreign Keys, and see which ones have "Cascade delete" turned on, you can run this script: 顺便说一句，要获取您的外键列表并查看打开了“级联删除”的外键，可以运行以下脚本：

SELECT 
   OBJECT_NAME(f.parent_object_id) AS 'Table name',
   COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Field name',
   delete_referential_action_desc AS 'On Delete'
FROM sys.foreign_keys AS f,
     sys.foreign_key_columns AS fc,
     sys.tables t 
WHERE f.OBJECT_ID = fc.constraint_object_id
AND t.OBJECT_ID = fc.referenced_object_id
ORDER BY 1

And if you ever find that you can't DROP a particular table due to a Foreign Key constraint, but you can't work out which FK is causing the problem, then you can run this command: 并且，如果您发现由于外键约束而无法DROP特定的表，但无法确定是哪个FK引起了问题，则可以运行以下命令：

sp_help 'TableName'

The SQL in that article lists all FKs which reference a particular table. 该文章中的SQL列出了引用特定表的所有FK。

Hope all this helps. 希望所有这些都对您有所帮助。

Apologies for the long finger. 为长手指道歉。 I was just trying to make a point. 我只是想指出一点。

---

#5楼

我认为您不能只是删除表属性，如果这是实际的生产数据，那么只需删除不影响表模式的内容。

---

#6楼

ON DELETE CASCADE
It specifies that the child data is deleted when the parent data is deleted. 它指定在删除父数据时删除子数据。

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE CASCADE
);

For this foreign key, we have specified the ON DELETE CASCADE clause which tells SQL Server to delete the corresponding records in the child table when the data in the parent table is deleted. 对于此外键，我们指定了ON DELETE CASCADE子句，该子句告诉SQL Server在删除父表中的数据时删除子表中的相应记录。 So in this example, if a product_id value is deleted from the products table, the corresponding records in the inventory table that use this product_id will also be deleted. 因此，在此示例中，如果从产品表中删除了product_id值，则库存表中使用该product_id的相应记录也将被删除。