If you love the R in RDBMS the way I do, you know how useful Foreign Keys are when enforcing relationships. (Update: Actually, I’ve since learned that the R in RDBMS refer to Relations which tables are an example of, not relationships between tables)
So if there’s a column that is supposed to participate in such a relationship (either as a surrogate key, or as a referring column), then you expect it to show up in the guts of some foreign key.
I wrote a script (which may or may not apply to your db) that queries the system views to tell me where I might be missing such foreign keys.
The Query:
SELECT t.name, c.name FROM sys.columns c INNER JOIN sys.tables t ON t.object_id = c.object_id INNER JOIN sys.indexes i ON i.object_id = t.object_id LEFT JOIN sys.foreign_key_columns fkc_Parent ON fkc_Parent.parent_column_id = c.column_id AND fkc_Parent.parent_object_id = c.object_id LEFT JOIN sys.foreign_key_columns fkc_Referenced ON fkc_Referenced.Referenced_column_id = c.column_id AND fkc_Referenced.Referenced_object_id = c.object_id LEFT JOIN sys.index_columns ic ON ic.index_id = i.index_id AND ic.object_id = t.object_id AND ic.column_id = c.column_id WHERE fkc_Referenced.constraint_object_id IS NULL AND fkc_Parent.constraint_column_id IS NULL AND ic.index_column_id IS NULL AND c.name LIKE '%id' AND i.is_primary_key = 1 ORDER BY t.name, c.name |
This query returns a candidate list of columns that hint at a potential table relationship with no corresponding foreign key. Specifically, this list is comprised of columns that are
- made up of columns with a name ending in %id. So this query applies to surrogate columns only for databases that follow this naming convention.
- non-referencing,
- non-referenced,
- not part of any primary key …
- … but are on tables that have primary keys
The whole point of this list is to make db designers think twice and ask themselves: “Do I need a foreign key here?”
I tried it out on AdventureWorks and there seems to be a missing key from Production.TransactionHistoryArchive(ProductID) to Production.Product(ProductID). As an archive table, maybe the missing foreign key is intentional. But again, it’s good to think twice about it.
Further Reading
Once you’ve created the new foreign keys, don’t forget to read my other article about Indexing for Foreign Keys
Whew! A quick Google search tells me that others have encountered the same problem and come up with a very similar solution.
Notably
Richard Dingwall: http://richarddingwall.name/2008/12/21/find-missing-foreignprimary-keys-in-sql-server/
and
George Mastros: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/missing-foreign-key-constraints
Comment by Michael J. Swart — December 7, 2009 @ 3:01 pm
[…] This post was mentioned on Twitter by Brent Ozar, Arjan Jonker. Arjan Jonker said: RT @BrentO: Slick query from Michael Swart on Finding your Missing Keys http://bit.ly/5ZGLNl […]
Pingback by Tweets that mention Finding your Missing Keys | Michael J. Swart -- Topsy.com — December 9, 2009 @ 9:41 am
Very cool. Thanks! I ran it against one database I designed here and was pleased to find no columns that should have had a foreign key constraint but didn’t. Two were from another database, one only had an after-the-fact loosely guessed correlation with the original data (based on fuzzy time matching), and two were from a saved trace table, columns ClientProcessID and SPID.
I can’t run it in my other database because it’s SQL 2000 but perhaps I’ll be able to upgrade that soon…
It’s really sad how major applications by multimillion-dollar companies can’t seem to be bothered to have FK relationships in their databases (cough cough Eclipsys Sunrise Care Manager, McKesson Horizon Patient Folder cough cough)
Comment by Emtucifor — December 11, 2009 @ 7:02 pm
@Emtucifor, thanks for the feedback. I think everyone can benefit from more focus on db integrity. Enforced by the db itself with fks and constraints.
I have never heard of a single instance of anyone going overboard with this sort of thing.
Comment by Michael J. Swart — December 11, 2009 @ 11:36 pm
[…] Finding your Missing Keys – Well defined relationships are a fundamental feature of effective databases and Michael J Swart (The Database Whisperer) shows how you can validate yours. […]
Pingback by Something for the weekend – SQL Server Links 11/12/09 | John Sansom - SQL Server DBA in the UK — December 12, 2009 @ 5:52 am
Some day you’ll have to convince me why I want FKs. ( it’s a debate that is always worth having… especially when I need to run a script to find out how much it’s going to cost to delete a row of data )
Comment by Richard Wallace — December 18, 2009 @ 2:28 pm
Sure thing Richard… I’ll bring my boxing gloves. 🙂
Comment by Michael J. Swart — December 18, 2009 @ 3:01 pm
I took a different approach and extended it. What do you think of this: http://m60freeman.blogspot.com/2011/09/finding-foreign-keys-without-indexes.html
Comment by Mark Freeman — September 2, 2011 @ 1:43 pm
This is awesome Mark, I left a comment at your blog, http://m60freeman.blogspot.com/2011/09/finding-foreign-keys-without-indexes.html I predict great things for your script in the future. Thanks for sharing!
Comment by Michael J. Swart — September 2, 2011 @ 2:12 pm
[…] was looking working on a script to find possibly missing foreign keys, when I stumbled upon Michael J Swart’s blog and it gave me exactly what I was looking […]
Pingback by Find missing Foreign Keys in SQL Server | Chris Straw — January 13, 2012 @ 7:23 am