How to DELETE without ON DELETE CASCADE (conflict REFERENCE constraint)

Question

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?

Answer 1

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!