Why would you create a foreign key and then disable it?

Question

Why would you create a foreign key and then disable it?

souLTower

SSCarpal Tunnel

Points: 4026
More actions
April 11, 2014 at 6:17 am

#285678

I have an application that has been around for a long time. Most of the FKs are disabled. referential integrity is being handled through triggers. Why would someone have done this?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

A guess, they couldn't get the delete of parent data to work even with a trigger in place while the FK constraints were in place. The fix, disable the constraints instead of setting up the application to delete the child records first, then the parent records.

To get the real answer you will have to ask the vendor of the application.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

souLTower (4/11/2014)
I have an application that has been around for a long time. Most of the FKs are disabled. referential integrity is being handled through triggers. Why would someone have done this?

Perhaps it was so that they could TRUNCATE the table which works fine when triggers are in place but not when FKs are in place.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

They turned them off to speed up a data load but never turned them back on?

I don't know. Pretty much blazing away at their feet though.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 4

Thanks all. I think the "couldn't make it work with delete triggers" is probably the answer. I took a look at a bunch of other tables and found some that don't have any referential integrity handling at all. Disabled FK and no triggers.

This thing's been around for 10 years. There's some work to do.

Thanks

ST

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Because they would rather have bad data in the database than track down those annoying FK errors?

ScottPletcher SSC Guru Points: 100262 More actions · Answer 6

Another possibility:

It also provides documentation of the FK relationships.

I suspect they wanted their application to be able to run on many different dbs. Some don't (or didn't) directly support FK relationships defined to the DBMS. By defining but disabling the FK in those that do allow them to be specified and disabled, they can document the relationships in the DBMS itself without modifying the way the app works.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

jeffrey yao SSCarpal Tunnel Points: 4354 More actions · Answer 7

At one time, I did this as well. The scenario was a complex environment with lots of FKs on a few 800+ million row tables, any update/insert/delete becomes slow and at the end, we have no choice but to disable FKs, and leaving data integrity to application layer logic.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

jeffrey yao (4/11/2014)
At one time, I did this as well. The scenario was a complex environment with lots of FKs on a few 800+ million row tables, any update/insert/delete becomes slow and at the end, we have no choice but to disable FKs, and leaving data integrity to application layer logic.

I can understand why a lot of people might go that route but I've never actually seen it speed things up if the application is actually checking for data integrity properly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 9

It's very possible that this happened when the application was converted from Access to SQL7. It was a good find because it caused me to look through the DB and identify some important tables that don't enforce referential integrity. I'll fix those in the same manner as the other tables. I'll create the FK relationships and disable them and will handle integrity through the triggers.

Are triggers faster than letting the DB handle the integrity through FK? I'll do a test but what has been your experiences?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 10

souLTower (4/12/2014)
It's very possible that this happened when the application was converted from Access to SQL7. It was a good find because it caused me to look through the DB and identify some important tables that don't enforce referential integrity. I'll fix those in the same manner as the other tables. I'll create the FK relationships and disable them and will handle integrity through the triggers.
Are triggers faster than letting the DB handle the integrity through FK? I'll do a test but what has been your experiences?

With the right indexes in place, usually, the internal referential integrity check performs much faster than triggers. Heck, I've seen it outperform triggers with no indexes.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

souLTower (4/12/2014)
It's very possible that this happened when the application was converted from Access to SQL7. It was a good find because it caused me to look through the DB and identify some important tables that don't enforce referential integrity. I'll fix those in the same manner as the other tables. I'll create the FK relationships and disable them and will handle integrity through the triggers.
Are triggers faster than letting the DB handle the integrity through FK? I'll do a test but what has been your experiences?

In general, I wouldn't ever use triggers for Referential Integrity unless you need DRI to another database, which can't be done with FKs. It reminds me of when someone wrote a CLR to do a modulus because they didn't know T-SQL.

That might be another reason why the FKs were disabled... tables may have been moved to another database somewhere in the history of the database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions