January 5, 2017 at 6:04 pm
I have a active, highly relational, OLTP database with about 750 tables. There are almost no foreign-key constraints defined in the dB, the application layer keeps track of most of this. Does this result in orphaned data from time to time? Yes, but it hasn't been an issue and that's not what I'm trying to address here.
I have recently been tasked with documenting all relationships in the dB. I would like to simply add foreign-key constraints so the dB is self-documented, but I do not want to inflict any new overhead on the database, nor do I want to find out that some parts of the app layer delete parent records before children; I merely want to document with an eye toward possibly enabling some constraints at some time in the future.
My question is: Is adding permanently disabled foreign-key constraints a good idea for documentation purposes? I can't imagine there would be any performance impact. Is there a scenario where disabled foreign key constraints are re-enabled, implicitly (for ex: when indexes are rebuilt, etc)? Has anyone done this or know of people who have? Any general thoughts about this approach to documenting relationships?
Thanks,
-Dave
January 6, 2017 at 6:20 am
dave-L (1/5/2017)
I have a active, highly relational, OLTP database
combined with
There are almost no foreign-key constraints defined in the dB
Effectively means that your database is not relational.
but I do not want to inflict any new overhead on the database
Nor gain the performance benefits that enforced referential integrity provides through the query optimizer which can take advantage of referential constraints to provide you with superior execution plans leading to better performance.
My question is: Is adding permanently disabled foreign-key constraints a good idea for documentation purposes? I can't imagine there would be any performance impact. Is there a scenario where disabled foreign key constraints are re-enabled, implicitly (for ex: when indexes are rebuilt, etc)? Has anyone done this or know of people who have? Any general thoughts about this approach to documenting relationships?
Just for documenting the database, this will work. There's nothing in the commands for an index rebuild would inherently lead to foreign key constraints being re-enabled. It completely depends on the commands sent to the database. It would require an explicit command to enable a disabled foreign key. Same thing goes for any other standard maintenance commands. If you don't issue a command to enable the foreign key, it won't be.
That said, you're sacrificing one of the most fundamental aspects of using a relational storage engine. Enforced referential integrity is not just about data integrity (although, that's pretty fundamental too). SQL Server, as a relational database management system, is designed around those relationships and they are used within the engine as a mechanism for speeding data access. Taking that away is a poor choice under the vast majority of circumstances (while there are exceptions to everything, exceptions are just that, exceptional).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply