One of the more important structures in a database are foreign keys. Referential Integrity (RI) rarely gets the publicity it deserves and is often pushed aside during the development period of a data driven application. Before a database leaves development (I would argue conceptual design, but I’m a stickler) we should confirm that all possible RI is in place. But how do we quickly do this without spending hours looking through Object Explorer?
Here are a couple queries that make use of the catalog views to point out potential misses in RI (Note, these queries assume SQL 2005 or higher).
First, we simply want to find tables that contain no foreign keys.
--Tables with no foreign keys
SELECT OBJECT_SCHEMA_NAME(t.[object_id], db_id()) AS SchemaName
,t.name AS ObjectName
FROM sys.tables AS t
LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.parent_object_id = t.[object_id]
WHERE fkc.constraint_column_id IS NULL
ORDER BY SchemaName, ObjectName;
This first query will simply point out objects for us. This is okay if you were the original author of the database and you know where potential references exist. If you were not the original author then we need to go a step further and see if there are primary key columns that could be referenced.
--Tables with no foreign keys that have potential matches
SELECT OBJECT_SCHEMA_NAME(t.[object_id], db_id()) AS SchemaName
,t.name AS ObjectName
,c.name AS ColumnName
,OBJECT_SCHEMA_NAME(pc.[object_id], db_id()) AS PotentialMatchSchemaName
,OBJECT_NAME(pc.[object_id]) AS PotentialMatchObjectName
,pc.name PotentialMatchColumnName
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON c.[object_id] = t.[object_id]
LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.parent_object_id = t.[object_id]
INNER JOIN (
SELECT c.[object_id], c.column_id, c.name
FROM sys.columns AS c
INNER JOIN sys.index_columns AS ic ON ic.column_id = c.column_id
AND ic.[object_id] = c.[object_id]
INNER JOIN sys.indexes AS i ON i.index_id = ic.index_id
AND ic.[object_id] = i.[object_id]
WHERE i.is_primary_key = 1
) AS pc ON pc.name LIKE '%' + c.name +'%'
AND pc.[object_id] <> c.[object_id]
AND pc.column_id <> c.column_id
WHERE fkc.constraint_column_id IS NULL
ORDER BY SchemaName
,ObjectName
,c.column_id
,PotentialMatchSchemaName
,PotentialMatchObjectName
,PotentialMatchColumnName;
With this query, we can look through potential matches and see if the primary key of those objects are the same value in our child object.
We could go even one step further and dynamically generate the foreign key creation.
--Dynamically build the foreign key for matches
SELECT OBJECT_SCHEMA_NAME(t.[object_id], db_id()) AS SchemaName
,t.name AS ObjectName
,c.name AS ColumnName
,OBJECT_SCHEMA_NAME(pc.[object_id], db_id()) AS PotentialMatchSchemaName
,OBJECT_NAME(pc.[object_id]) AS PotentialMatchObjectName
,pc.name PotentialMatchColumnName
,'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], db_id()))
+ '.' + QUOTENAME(t.name)
+ ' ADD CONSTRAINT FK_'
+ t.name + '_' + OBJECT_NAME(pc.[object_id])
+ '_' + pc.name AS CreateFKStatement
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON c.[object_id] = t.[object_id]
LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.parent_object_id = t.[object_id]
INNER JOIN (
SELECT c.[object_id], c.column_id, c.name
FROM sys.columns AS c
INNER JOIN sys.index_columns AS ic ON ic.column_id = c.column_id
AND ic.[object_id] = c.[object_id]
INNER JOIN sys.indexes AS i ON i.index_id = ic.index_id
AND ic.[object_id] = i.[object_id]
WHERE i.is_primary_key = 1
) AS pc ON pc.name LIKE '%' + c.name +'%'
AND pc.[object_id] <> c.[object_id]
AND pc.column_id <> c.column_id
WHERE fkc.constraint_column_id IS NULL
ORDER BY SchemaName
,ObjectName
,c.column_id
,PotentialMatchSchemaName
,PotentialMatchObjectName
,PotentialMatchColumnName;
With the queries above in hand you can give yourself a pretty good indication of the work necessary to have solid RI in place for your database.
f757df73-0ab0-4abc-903b-929ecb058209|2|5.0