我有2个表:T1和T2，它们是现有的数据表。T1和T2之间有一对多的关系。当删除T1中的记录时，如何修改表定义以在SQL Server中执行级联删除，T2中的所有关联记录也被删除。
外国的约束在他们之间存在。我不想删除表或创建触发器来删除T2。例如，当我删除一个员工时，所有的评审记录也应该消失了。
T1 -员工，
Employee ID
Name
Status
T2 -工作表现检讨，
Employee ID - 2009 Review
Employee ID - 2010 Review
当前回答
首先启用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
其他回答
首先启用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
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.
我认为你不能只是删除表属性，如果这是实际的生产数据，只是删除不影响表模式的内容。
你需要，
删除现有的外键约束， 添加一个启用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
删除级联 它指定在删除父数据时删除子数据。
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的相应记录也将被删除。
