Showing posts with label Foreign Keys. Show all posts
Showing posts with label Foreign Keys. Show all posts

3.3.15

SQL Server, Error message 1785, and cascading deletes: How it kills kittens

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. 

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.

7.11.13

Do your Foreign Keys Hate You?

We all love our databases. We trust everything they do! How could we not? It's our baby.... well, probably not. How do we know when it's sneaking around behind your back asking questions it shouldn't and refusing to follow the plans? It's good to occasionally check up on certain aspects of your database. The Ozar family has a wonderful script called sp_BLITZ that does a fantastic job of identifying if you have some of the more common database issues and gives good general information.

All this is, is an easy way to have a script build a script for you to clear up what problems you may have. you're mileage may very, all sales final, returns not permitted, penguins accepted as payment..

If you're really curious what all this entails, there's quite a few sites with good information about these evil foreign keys we just cannot trust.

This is just a basic overview. When a key is trusted, it doesn't check the other table to ensure all the data matches, it just trusts it. When it's not trusted, you're doubling that work.

You could have had to disable that relationship at some point to remove a bad record, add in new data, or just simply because you were curious what happens when you disable it.

below is a script that will isolate what foreign keys are not trusted. It also provides a basic script that will make it trusted if everything matches up. It will error if your constraint is unable to be trusted.

exec sp_MSforeachdb @command1 ='
use ?
declare @DBName VarChar(300)
set @DBName = (select ''use '' + (Select DB_Name()))
select s.name as SchemaName, o.name as TableName, i.name as IndexName, o.object_id as Item,
@DBName + ''; '' + ''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] WITH CHECK CHECK CONSTRAINT ['' + i.name + '']'' as Repair_Script
from sys.foreign_keys i
inner join sys.objects o on i.parent_object_id = o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
where i.is_not_trusted = 1 and i.is_not_for_replication = 0
'