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.
© 2020, John Morehouse. All rights reserved.
I found a few problems with this article. The terms “parent” and “child” come from the old network databases and refer to pointer chains. In RDBMS the terms I think you’re looking for are “referenced table” and “referencing table” instead. The referenced and referencing table can be the same table. The references can be circular. In short, this is a far more general concept than traversal model which we had a network databases.
Also, the referenced table columns do not have to be the primary key; any key (UNIQUE, NOT NULL) will do which let us build very elaborate referenced systems.
Rows are not records; row is a logical concept that may or may not have a physical existence, but a record is physical. In SQL, we use triggers (a really bad choice) or declarative referential integrity, DRI. This is done with the references clause that has optional subclauses for “on delete ” and” on update “. The action can be to cascade the deletion or update, set the action to a default value or null, or to disallow the action. The reason we added these features to SQL was we found that 80 to 90% of the triggers that people were writing did these actions. When we made them part of the language, the optimizer could get a hold of them and make decisions that could not be done with the procedural code in triggers.