問題描述

我有一個巨大的遺留數據庫，其中一個表有多個指向另一個表的外鍵，并且看不到一個級聯，類似于下面的示例表:

創建表用戶(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 約束)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！