我有2个表:T1和T2，它们是现有的数据表。T1和T2之间有一对多的关系。当删除T1中的记录时，如何修改表定义以在SQL Server中执行级联删除，T2中的所有关联记录也被删除。
外国的约束在他们之间存在。我不想删除表或创建触发器来删除T2。例如，当我删除一个员工时，所有的评审记录也应该消失了。
T1 -员工，
Employee ID
Name
Status
T2 -工作表现检讨，
Employee ID - 2009 Review
Employee ID - 2010 Review
使用像这样的东西
ALTER TABLE T2
ADD CONSTRAINT fk_employee
FOREIGN KEY (employeeID)
REFERENCES T1 (employeeID)
ON DELETE CASCADE;
填写正确的列名，就可以完成设置。正如mark_s正确地指出的那样，如果已经有一个外键约束，可能需要先删除旧的外键约束，然后再创建新的外键约束。
你需要，
删除现有的外键约束， 添加一个启用ON DELETE CASCADE设置的新对象。
喜欢的东西:
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
首先启用ONCascade属性:
1.删除现有的外键约束
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
关闭ONCascade属性:
1.删除现有的外键约束
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
您可以使用SQL Server Management Studio完成此操作。
→右键单击表格设计，在左侧窗格中选择关系，在右侧窗格中选择外键，展开菜单“INSERT and UPDATE specification”，并选择“Cascade”作为删除规则。
使用实例在SQL Server Management Studio中添加“Cascade delete”外键。
首先，选择你的外键，并在一个新的查询窗口中打开它的“DROP and Create To.”。
然后，只需添加ON DELETE CASCADE到add约束命令:
然后点击“执行”按钮来运行这个查询。
顺便说一下，要获得一个外键列表，并查看哪些外键开启了“Cascade delete”，你可以运行这个脚本:
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
如果你发现由于外键约束，你不能DROP一个特定的表，但你不知道是哪个FK导致了这个问题，那么你可以运行这个命令:
sp_help 'TableName'
那篇文章中的SQL列出了引用特定表的所有fk。
希望这些能有所帮助。
抱歉我的手指太长了。我只是想表达我的观点。
删除级联 它指定在删除父数据时删除子数据。
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
);
对于这个外键，我们已经指定了ON DELETE CASCADE子句，它告诉SQL Server在父表中的数据被删除时删除子表中的相应记录。因此，在本例中，如果从products表中删除了product_id值，那么库存表中使用该product_id的相应记录也将被删除。
If the one to many relationship is from T1 to T2 then it doesn't represent a function and therefore cannot be used to deduce or infer an inverse function that guarantees the resulting T2 value doesn't omit tuples of T1 join T2 that are deductively valid, because there is no deductively valid inverse function. ( representing functions was the purpose of primary keys. ) The answer in SQL think is yes you can do it. The answer in relational think is no you can't do it. See points of ambiguity in Codd 1970. The relationship would have to be many-to-one from T1 to T2.
