THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Should my database have foreign key constraints?

This blog has moved! You can find this content at the following new location:

http://greglow.com/index.php/2016/05/31/should-my-database-have-foreign-key-constraints/

Published Tuesday, May 31, 2016 12:59 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

sqlblindman said:

This is part of the long-running debate (feud) regarding whether business rules should be included in the database. Constraints are business rules, essentially.

The debate goes away (or should) when one differentiates between data-oriented business rules and interaction-oriented business rules. Data-oriented business rules should be enforced as closely to the data as possible (datatypes, constraints, triggers, sprocs and views, in that general order) and interaction-oriented business rules should be enforced as close to the interface as possible.

May 31, 2016 12:49 PM
 

Greg Low said:

In the end, data types are constraints too, so where should the line be drawn? I like your distinction of data-oriented business rules vs interaction-oriented. Given most architectures have at least 3 layers though, I'm not sure that I'd call the others interaction-oriented. Perhaps interaction and application logic oriented?

May 31, 2016 6:40 PM
 

Roman I said:

Interestingly, most of existing BIG ERPs do NOT run with referential constraints.

SAP does not:

"However, many Enterprise Database Applications (like SAP) have their own proprietary metadata repository (such as the SAP Data Dictionary, or DDIC) that allows for self-management of referential integrity relationships, cluster tables, etc.   The self-management means it does not rely on the RDBMS to provide the Declarative Referential Integrity (DRI) to manage the Primary Key and Foreign Key relationships.  Almost all SAP database tables have Primary Key constraints specified but without any foreign keys that are associated to them.   This is significant in that there is no way for the database to assess the logical consistency between tables and their data when those relationships aren’t defined.   That can only be done within the realm of the SAP Application module itself."

from here:

https://blogs.msdn.microsoft.com/saponsqlserver/2013/10/30/corruption-handling-in-sap-databases/

MS Dynamics AX also does NOT have referential constraints in database - 2500+ tables and no ref checks. And many others are the same. I worked for mid-market ERP company, we had 1200+ tables solution - with no ref constraints.

Wherever you work, most likely your paycheck was not computed and stored with referential constraints... That really SUXXXX... These systems handle financial data and run big businesses..

June 4, 2016 2:35 PM
 

Roman I said:

(continuing)

hard to advocate for ref constraints in smaller apps when such an examples are well known.

The cause I think is because these big systems are built using special proprietary languages (ABAP for SAP, X++ for dynamics AX, etc). And these languages just did not give much support to 'delayed save-all' which allows data access layer to proper sort the updates. Every record is saved individually and immediately from code; the main reason for this is reliance of identities as PK, so we need record ID immediately, to start linking other entities to it. Using sequences (instead of identities) would have helped, but sequences appeared in SQL Server only recently.

So it all rolled out over the years into the mess we have now. And the result is of course - real mess in data; orphan records, pointers pointing to nowhere, etc. This is in real ERP apps that run big multi-nationals!

One quite easy fix could be delayed ref constraint check that you mention - then all this stuff would start working (or most of it).

But MS stupidly does not understand the importance of this SQL-92 feature. Sad....

June 4, 2016 2:44 PM
 

Jaosn Hopkins said:

The split is data integrity vs. business rules; referential integrity being an example of the former. An Invoice without a Customer is incoherent (cannot be usefully interpreted or accounted for). "This status is only valid for this customer type under these conditions" is a business rule, and does not impair the ability to interpret and use the data even if violated.

Data integrity is the responsibility of the data people. If you do not have data people- well, there's your problem!

June 4, 2016 3:12 PM
 

PiMané said:

Many people don't use it because of performance issues...

They create the unique index on the column to build the FK but forget to build the "inverse" index...

Imagine a Taxes table... Usually the table has few rows... very few (10, 20, 100 tops..). The invoice line table has a taxId and has lots and lots of rows (lets say 10.000.000 for a medium database..). The FK only requires an unique index on the taxId of the Taxes table... It doesn't require and index on the invoice line table..

If you delete a tax from the Taxes table the database will do a scan on the entire invoice line table to check if it's used.. Our ERP had this situation..

Whenever you tried to delete a tax it took more than 10minutes... We created the "reverse" index and it was immediate...

June 6, 2016 6:35 AM
 

Greg Low said:

Hi Jason, for that reason, when we review system designs, if we find a declared foreign key where the key columns aren't the left-most component of at least one non-clustered index, we consider that a code-smell.

It's why I've previously blogged that Microsoft should create default indexes on foreign key constraints unless you use the "I know what I'm doing" option.

June 6, 2016 6:38 AM
 

Paul Nielsen said:

I strongly agree with you Greg. Many years ago I published a scrip to automatically generate indexes for FKs.

http://sqlblog.com/blogs/paul_nielsen/archive/2007/02/08/codegen-to-create-indexes-for-fks.aspx

I was surprised when another very well known SQL MVP told me later that he thought is was a stupid idea to assume that FKs should have indexes. It's a very rare FK that doesn't benefit from an index.

July 28, 2016 11:13 PM
 

Greg Low said:

I received this query via email:

I have a question. Been a dba for years, prior job had performance issues with high volume inserts, I found a few fk's were slowing things from the redundant checking and turned them off because the code handled the quality, things sped up. In my current job we have perf complaints, basically every single table has them, one table had 200+(fk's on one table), with the top 13 tables having over 500 fk's(an average of 42 per table). They now temporarily disable them for some processes, which sped things up, so clearly there is a cost to them. I've been pushing to have the models simplified and keys for critical, and not every table. I am fine with that model but but can there be too many fk's?

August 7, 2016 2:25 AM
 

Greg Low said:

In general I find very few FKs that slow performance in enough of a notable way to made me decide to trade that for integrity. Even then, I only disable (not delete) the ones that are *proven* to be a problem.

However, if I had tables with 200 or more FKs on a single table, I'd suspect I had much bigger problems than the FKs. The model seems to be the real issue in your case.

August 7, 2016 2:28 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement