5

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?

TT.
  • 15,774
  • 6
  • 47
  • 88
Ilya Kozhevnikov
  • 10,242
  • 4
  • 40
  • 70
  • Consider the following: you want to delete user with `ID=3`. There is a product that is created by this user and was updated by user with `ID=5`. Suppose you would delete that product as part of the cascade when you delete user `3`, then you need to query all products updated by `ID=5`. You're going to miss out on one product! Alternatively you could keep the product and set the `createdBy` to `NULL` so you don't miss out on important information. But that is no cascade delete of course. You need to ask yourself if you really want to delete that user, and if there aren't any better options. – TT. Nov 05 '16 at 08:03

1 Answers1

5

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!

Community
  • 1
  • 1
Codesleuth
  • 10,321
  • 8
  • 51
  • 71
  • Thanks, as mentioned in OP predeleting references is not really an option, db is absolutely massive and it's not production functionality to invest signification resources into stepping through hundreds of tables. I didn't want to disable all constraint checks cause I do want all that rubbish associated with deleted User to be deleted as well and not hang around referenceless. – Ilya Kozhevnikov Dec 19 '12 at 12:10