如何在没有 ON DELETE CASCADE 的情况下进行 DELETE(冲突 REFERENCE 约束) [英] How to DELETE without ON DELETE CASCADE (conflict REFERENCE constraint)

问题描述

我有一个巨大的遗留数据库，其中一个表有多个指向另一个表的外键，并且看不到一个级联，类似于下面的示例表:

创建表用户(id int 主键标识，名称 varchar(max))创建表产品(id int 主键标识，名称 varchar(max),CreatedBy int 外键引用 Users(Id),UpdatedBy int 外键引用 Users(Id))插入用户值('Bar')插入用户值('Baz')插入产品值('Foo', 1, 2)

我需要能够删除一些旧数据，但它当然会抛出引用异常:

 从用户中删除 Name='Bar'

<块引用>

DELETE 语句与 REFERENCE 约束FK__Products__Create__1AD3FDA4"冲突.冲突发生在数据库Foo"、表dbo.Products"、CreatedBy"列中.

<小时>

由于数据库的绝对复杂性，我无法预先删除所有引用，因此我尝试通过级联设置以编程方式添加临时外键以解决它们.但是，对于这个具有多个指向另一个表的外键的特定表，这会导致第二个 UpdatedBy 上的 cycles or multiple cascade paths 改变:

alter table Products 添加外键 (CreatedBy) 在删除级联上引用 Users(Id)更改表产品在删除级联上添加外键 (UpdatedBy) 引用 Users(Id)

<块引用>

在表 'Products' 上引入 FOREIGN KEY 约束 'FK__Products__Update__1DB06A4F' 可能会导致循环或多个级联路径.指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束.

<小时>

如何通过某种方式解决多个级联路径问题或以其他方式使 delete from Users where 工作同时保持参照完整性?

解决方案

我个人不会这样做(我会预先删除所有引用的数据并手动检查完整性).请参阅:是否可以使用 T- 暂时禁用外键约束SQL?

引用:

-- 禁用所有约束EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

在禁用约束后删除您的数据，但记得之后重新启用它们！

-- 启用所有约束EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

另请注意，存储过程 sp_msforeachtable 未记录，可能会在 SQL Server 的未来版本中消失.

如果您不想全面禁用约束(也许您有一个适用的表的列表)，那么只需像上面的代码中看到的那样禁用它们即可.

ALTER TABLE [产品] NOCHECK CONSTRAINT ALLDELETE FROM [Users] where Name='Bar'ALTER TABLE [产品] WITH CHECK CHECK CONSTRAINT ALL

所有功劳都归于 kristof 的回答.请投票！

I have a gigantic legacy database with a table that has multiple foreign keys pointing to one other table and with not a single cascade in sight, similar to sample tables below:

create table Users (
    Id int primary key identity,
    Name varchar(max)
)

create table Products (
    Id int primary key identity,
    Name varchar(max),
    CreatedBy int foreign key references Users(Id),
    UpdatedBy int foreign key references Users(Id)
)

insert into Users values('Bar')
insert into Users values('Baz')
insert into Products values('Foo', 1, 2)

I need to be able to to delete some of the old data, but it of course throws reference exceptions:

delete from Users where Name='Bar'

The DELETE statement conflicted with the REFERENCE constraint "FK__Products__Create__1AD3FDA4". The conflict occurred in database "Foo", table "dbo.Products", column 'CreatedBy'.

---

Due to the sheer complexity of the database I can't predelete all of the references, so I'm trying to add temporary foreign keys programmatically with cascades set to resolve them. However, for this particular table that has multiple foreign keys to one other table, this results in cycles or multiple cascade paths on the second UpdatedBy alter:

alter table Products add foreign key (CreatedBy) references Users(Id) on delete cascade
alter table Products add foreign key (UpdatedBy) references Users(Id) on delete cascade

Introducing FOREIGN KEY constraint 'FK__Products__Update__1DB06A4F' on table 'Products' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

---

How can I make delete from Users where work while maintaining referential integrity, either by somehow getting around multiple cascade path issues or otherwise?

解决方案

Personally I would not do this (I would pre-delete all the referenced data and manually check the integrity). See: Can foreign key constraints be temporarily disabled using T-SQL?

Quote:

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Delete your data once the constraints are disabled, but remember to turn them back on again afterwards!

-- enable all constraints
EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Also note that the stored procedure sp_msforeachtable is undocumented and may disappear in future releases of SQL Server.

If you'd rather not blanket-disable constraints (perhaps you have a list of the tables that apply) then simply disable them as you can see in the code above.

ALTER TABLE [Products] NOCHECK CONSTRAINT ALL
DELETE FROM [Users] where Name='Bar'
ALTER TABLE [Products] WITH CHECK CHECK CONSTRAINT ALL

All credit goes to kristof's answer. Please up vote!

这篇关于如何在没有 ON DELETE CASCADE 的情况下进行 DELETE(冲突 REFERENCE 约束)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！