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 |
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
