G’day,
One of the things that I’ve been seeing more often than I would like to lately is large databases with no foreign keys – or minimal foreign keys (and often with those minimal set of keys disabled)
By large databases I’m roughly meaning databases with several hundred tables, and I usually see a lot of these tables with several hundred GB’s of data in them.
When I generally ask about the reason for no foreign key, I’m told
- they add overhead
- they give no benefit
- we can’t enter our data properly when we have them
The last one in the above list is generally down to poor modelling – an example being entering a later part of a financial transaction before the first part of the financial transaction has taken place. Once both parts of the financial transaction have taken place then the database is left in a consistent state – BUT, that generally being handled by the app NOT the database – OUCH!
My first though here is “I’m glad I don’t use this company” – and I also wonder why auditing or testing has not picked it up. Quite often it has but somebody, somewhere has decided that there’s a reasonably low chance of that occurring and so no action has been taken – until the ‘bug’ is discovered sometime later and at that point takes a long time – and a lot of money – to fix.
Points 1 and 2 are quoted more often than I would like also – usually stemming from statements that were made by somebody who worked with a long dead version of the database and didn’t quite take the time to understand fully how relational databases work – but who had tremendous influence in the company and a reputation that meant they were not to be crossed. (they have since been proved wrong but have failed to accept it)
These are simply examples of now bad culture can lead to problems years down the road.
Looking at a lot of database that lacked foreign keys, it’s usually pretty simply to find examples of inconsistent data in the tables. – caused by the three issues above.
The moral of the story here is
Always question, always learn and really try to avoid issues that are bound to come back and haunt you.
Always try to build good culture.
Have a great day.
Cheers
Martin.
actually, FK add to data consistency as well as performance !!
Support FK with appropriate indexes ( as long as they don’t notably hurt performance )
check : https://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/
Or how about: it’s a BI data warehouse, not a relational database. FK is enforced via nature of etl processes. Why have overhead when dealing with millions/billions of rows.
Also makes doing database admin tasks a pain with DK relationships.
And to verify all is good, having a periodic process to double check no orphan keys.
Do you see any problem with that? I don’t.
And I assume many more cases / reasons exist
I once consulted at a major income tax return company that had absolutely no foreign keys in their hundreds of tables database. As a consultant, the first thing I typically do is build a diagram of the database, or at least the portion of it that I need to work with, so I can see the relationships between the tables. When I did this I got what looked like a blank diagram, which was really puzzling. Was there something wrong with SSMS? Then I dusted off my tired eyes and notice a faint series of tiny dots forming a straight line running across the middle of the screen. When I zoomed in about 100 times, there were all the tables, neatly lying side by side because there were no foreign keys to give any structure to the diagram.
Completely agree. This is all too common even with “professionally” developed software. Lots of bad DBAs out there who can’t make the case for constraints.
It is discouraging, to meet “senior developers” (i.e., 10+years) who do not know what a foreign key is, how to define one, or have the vaguest idea of the benefits they provide.
I have had several whose entire attitude to FKs is based on the “it’s to difficult to delete rows…”.
Famous Naval saying:
Measure with a Micrometer, Mark with Chalk, Chop with Ax.