I've been gone for a
bit, forgive the long delay. Who would have thought the hatred for a cascading
delete would cause an update? Strange isn't it? So where to begin?
It was a dark and stormy
night... Wait, wrong movie.
I just finished a design
on a project for cascading deletes. We have over 100 tables that spider web out from
our main claim table.
Problem: We cannot
delete a record from the main table without deleting from all of the other tables
first, in the right order.
Solution: Modify all of the Foreign Keys (FK) to include ON
DELETE CASCADE.
I wanted to share some
of my pains of labor with you all. ^.^;
Issue 1) We had one
table with FK’s to two other tables. This prevented us from using the ON DELETE
CASCADE because that table had protection from deletion by another FK. Pain in
the tail.
This cause the error "Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'fk_two' on table 'table2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors."
This error is because of how a cascading path works. You have to have all the deletes/updates flow in a single direction. Having a FK to another table other than your main table will cause it to be protected by another key.
We decided that we’d remove the secondary FK since it’s already has the constraint from the primary table… We were able to just drop the secondary constraint since it had no children related to anything else. This still kept it under the original tables constraint.
This cause the error "Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'fk_two' on table 'table2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors."
This error is because of how a cascading path works. You have to have all the deletes/updates flow in a single direction. Having a FK to another table other than your main table will cause it to be protected by another key.
We decided that we’d remove the secondary FK since it’s already has the constraint from the primary table… We were able to just drop the secondary constraint since it had no children related to anything else. This still kept it under the original tables constraint.
Issue 2) We were lucky
enough to have a Visio diagram of this whole 100+ table process. If we did not have this already on hand, we would have used the show dependency feature to start the spider web out. It would have worked
fine.
Issue 3) Rewriting all
the FK’s will take a tremendous amount of time. I created a script to help with
that. It’s a bit convoluted, so bear with it.
select *
into #results
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
select
'ALTER TABLE ['+sch.name+'].['+tbl.name+'] DROP CONSTRAINT ['+fk.name+'] ALTER TABLE ['+sch.name+'].['+tbl.name+'] WITH CHECK ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY(['+kcu.COLUMN_NAME+']) REFERENCES ['+sch2.name+'].['+tbl2.name+'] (['+temp.COLUMN_NAME+']) on delete
cascade ALTER TABLE ['+sch.name+'].['+tbl.name+'] CHECK CONSTRAINT ['+fk.name+']'
from sys.sysforeignkeys sfk
join sys.foreign_keys fk on fk.object_id = sfk.constid
join sys.tables tbl on tbl.object_id = fk.parent_object_id
join sys.schemas sch on sch.schema_id = tbl.schema_id
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.TABLE_NAME = tbl.name
join sys.tables tbl2 on tbl2.object_id = sfk.rkeyid
join sys.schemas sch2 on sch2.schema_id = tbl2.schema_id
join #results
temp on temp.TABLE_NAME = tbl2.name
WHERE OBJECTPROPERTY(OBJECT_ID(kcu.constraint_name), 'IsPrimaryKey') = 0
order by tbl.name
drop table #results
Issue 4) The script
output isn't in the right order. We then took the diagram from Issue 2, started
on all the outside spider webs and put them in order in the script. Using the "Show Dependency" option on the table, it helps see how many paths down it is. This would be a great time to build a diagram if you don't already have one.
Issue 5) While updating
a FK, It locks both the table with the FK and the table with the PK. This table never stops updating, so it
will lock it for a while its running. Luckily, with all SSDs, The worst table
is 2m30s.
Final Result. It took 3-4 weeks total for this whole project. It used to take us a 5 day work
week to remove 1400 records. It now takes 5 minutes to remove those same 1400.
Things to consider: Cascading
deletes reduces integrity: Before if you tried to delete a claim, it would
fail. Now it works. That’s the easier scenario to fix. If someone deletes
something from the 2nd Tier, it may go unnoticed. The negative side of
this has to be discussed. Proper access control could be enough.
While you’re doing this,
do you want to add cascading updates as well? It will take the same time. It
has the same issues that cascading deletes have. We decided that it wasn't worth the risk for updates as well.
by the sounds of it, if this was a late enough sql version I would create a DELETE_mytable_USING_filter SP and pass in a TVP with the filter of Id's (or other identifiers). This way you have predictable locking, continued protection from FKs and a simple way to clear a slice of data
ReplyDeleteI tend to only use cascading deletes where a structure has a head and related tables that are true hierarchical contents of that entity. These tend to be shallow and so the negatives have less impact.
Interesting, Would you still avoid the cascading deletes in this case? All tables have a single head or are a subtier of that head. The one table that had a FK removed, had a direct connection to the main table still. All tables only relate to the main tables data.
Delete