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.
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.
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
'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
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.