Finding Foreign Key Child Records In SQL Server

Foreign keys help to ensure referential integrity between tables. In other words, parent records cannot be deleted if there are child records present. This is a great thing and if you aren’t using foreign keys currently, you really should be. While very helpful with referential integrity foreign keys can introduce slowness when deleting data, especially if you are wanting to delete the parent record. In order for the transaction to complete, SQL Server has to check all foreign keys to make sure there aren’t any child records present.

 

Recently, I had to purge some parent records from a table. In this case, the parent table had foreign keys, which itself isn’t an issue. The fact that there were more than 30 of them was. While SQL Server will happily tell you that you are violating a foreign key if a child record is present when deleting the parent record, finding all of them can be cumbersome. This is even more true when you have a larger number of foreign keys.

 

Thankfully, SQL Server can tell us a lot of information about foreign keys including both the parent and child tables as well as the column used. From this information, we can dynamically create a SELECT statement that would tell us the number of child records that are tied to the parent ID.

 

Tables

 

We can use the following system tables to help generate our SELECT statement.

 

Sys.foreign_key_columns

 

This table tells us which column is used in the foreign key definition. We can also use this table to determine the parent table as well as the child table.

 

Sys.columns

 

When we join the previous table to sys.columns we obtain the column names which is then used in the ultimate SELECT statement. We use the column names to construct the JOIN statement that we need to join the parent table to the child table.

 

Sys.objects

 

Many applications use different schemas for various reasons. We have to account for this and using sys.objects allows us to determine the schema name of both the parent and child tables. The schema name is used in the dynamic query to ensure that we JOIN the right tables together.

 

The Query

 

Now that we know the foundation tables that we need, we can build out a SELECT statement that will tell us

• Parent table name
• The column name used in the parent table
• The child table name
• The column name used in the child table
• A SELECT statement

-- Parent Table
DECLARE @tableName VARCHAR(150) = 'dbo.Product'

SELECT
    OBJECT_NAME(fkc.referenced_object_id) AS 'Parent Table'
    , parentcolumns.name AS 'Parent Column'
    , OBJECT_NAME(fkc.parent_object_id) AS 'Child Table'
    , childcolumns.name AS 'Child Column'
    , ' SELECT COUNT(1) as ''RowCount'', ''' + OBJECT_NAME(fkc.parent_object_id)+ ''' as ''ChildTable'' from ' + quotename(schema_name(o1.schema_id)) + '.' + object_name(fkc.referenced_object_id) + ' x
            INNER JOIN ' + quotename(schema_name(o2.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) + ' y ON x.' + parentcolumns.name + ' = y.'+ childcolumns.name + ' UNION'
FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns childcolumns ON fkc.parent_object_id = childcolumns.object_id AND fkc.parent_column_id = childcolumns.column_id -- get child columns
    INNER JOIN sys.columns parentcolumns ON fkc.referenced_object_id = parentcolumns.object_id AND fkc.referenced_column_id = parentcolumns.column_id -- get parent columns
    INNER JOIN sys.objects o1 ON fkc.referenced_object_id = o1.object_id -- get the parent schema name
    inner JOIN sys.objects o2 on fkc.parent_object_id = o2.object_id -- get the child schema name
WHERE fkc.referenced_object_id = OBJECT_ID(@tableName)

If you wanted to see all of the tables, you can omit the WHERE clause in the above query. Let’s take a look at the Product table in AdventureWorks2014.

 

 

The SELECT statement is generated using a UNION at the end of it. This will allow you to run the entire block of statements and have it returned into a single data set. You will have to remove the final UNION otherwise the query will error out.

 

Results

 

From the image below, we can now see all of the child tables that have corresponding records to the parent foreign key. This information is helpful to determine what children rows might need to be deleted in order to remove parent records.

 

 

Ad-hoc Tables

 

You can also adjust the query to find records for a particular data set. In other words, if you had a sub-set of unique identifiers based on the foreign key definition you can adjust the query such that you find any child records based on those values.

 

In this example, I’m using a table variable just for ease of the demo, but this could easily be a physical table or a temporary table. Just remember that a table variable or a temporary table would need to be created within which ever session that you run the larger select statements.

-- Parent Table
DECLARE @tableName VARCHAR(150) = 'Production.Product'
DECLARE @IDs TABLE (ids int)

INSERT @IDs (ids)
    SELECT 316 UNION
    SELECT 317 UNION
    SELECT 318 UNION
    SELECT 319 UNION
    SELECT 320

SELECT
    OBJECT_NAME(fkc.referenced_object_id) AS 'Parent Table'
    , parentcolumns.name AS 'Parent Column'
    , OBJECT_NAME(fkc.parent_object_id) AS 'Child Table'
    , childcolumns.name AS 'Child Column'
    , ' SELECT COUNT(1) as ''RowCount'', ''' + OBJECT_NAME(fkc.parent_object_id)+ ''' as ''ChildTable'' from @ids x
            INNER JOIN ' + quotename(schema_name(o2.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) + ' y ON x.ids = y.'+ childcolumns.name + ' UNION'
FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns childcolumns ON fkc.parent_object_id = childcolumns.object_id AND fkc.parent_column_id = childcolumns.column_id -- get child columns
    INNER JOIN sys.columns parentcolumns ON fkc.referenced_object_id = parentcolumns.object_id AND fkc.referenced_column_id = parentcolumns.column_id -- get parent columns
    INNER JOIN sys.objects o1 ON fkc.referenced_object_id = o1.object_id -- get the parent schema name
    inner JOIN sys.objects o2 on fkc.parent_object_id = o2.object_id -- get the child schema name
WHERE fkc.referenced_object_id = OBJECT_ID(@tableName)

Taking the resulting SELECT statement from the above query, removing the last UNION, and executing, we can then see where child rows exist within the children tables. We can then go and address those rows before deleting the parent rows.

 

 

Summary

 

SQL Server holds a lot of information on the underlying structures of database objects. It’s just a matter of knowing where to find the information to build out a solution. In this case, this script helped me to find and handle a number of children records so that I could finish up deleting their parent records.

 

It should go without saying, however, run that at your own risk and don’t blindly run code you download from the internet.