Last week I wrote a SQL query to estimate how many columns are missing from foreign or primary keys. This works because of our naming convention for database keys:
- We use a Code suffix for natural keys e.g. CountryCode = NZ
- We use an ID suffix for surrogate keys e.g. EmployeeID = 32491
This script looks for any columns that match this naming pattern, but aren't part of a primary or foreign key relationship.
Using this script, I found over 200 missing foreign keys in one production database!