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.
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.
- 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.
Once you’ve created the new foreign keys, don’t forget to read my other article about Indexing for Foreign Keys