Finding, disabling and enabling foreign keys3
June 29, 2016 by Kenneth Fisher
Foreign keys are a classic method of enforcing RI (Referential Integrity). Unfortunately though, they can get in the way if you have to reload data in a table. Lets say developers would like you to overwrite the data from several tables in the test environment with data from the production environment (ignoring security issues here). Unless you know the correct order to delete and re-load you are going to run into referential integrity issues. Ie you have to delete children before parents, and load parents before children.
And one more example to make this easy to understand. You have two tables, Order and OrderDetail. Order has a primary key of OrderId, OrderDetail also has an OrderId column and has a foreign key that requires the Order exist before you can load the OrderDetail.
CREATE TABLE [Order] ( OrderId INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_Order PRIMARY KEY, OtherCols varchar(50) ) CREATE TABLE OrderDetail ( OrderDetailId INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_OrderDetail PRIMARY KEY, OrderDetail INT NOT NULL CONSTRAINT fk_Order FOREIGN KEY REFERENCES [Order](OrderId), OtherCols varchar(50) )
I have to be certain to delete from OrderDetail first, then Order (to maintain the RI) and then load Order first then OrderDetail. No big deal in this simple example, but what if I’m dealing with a dozen tables? Or I’m only re-loading the parent (Order)?
The easiest thing to do is to disable the foreign key, load your data, and then re-enable the foreign key. You might be tempted to skip that last step but don’t. RI is very important and in fact a trusted foreign key can be used by the optimizer to improve your query plan. It’s easy to say that the application doesn’t have bugs that cause problems with referential integrity. It’s not like you’re ever going insert an OrderDetail without an Order right? Unfortunately it’s far to easy for mistakes to happen. Maybe not in the application, it might be a mistake in an update meant to fix something else. Our job is to protect the data, and RI is an important part of that. So by all means disable a foreign key to help with a load but make sure you turn it back on when you are done.
The query below will return a list of all foreign keys for the database, or you can include the WHERE clause to pull just the foreign keys related to a single table. It uses the sys.foreign_keys system view at it’s base.
SELECT name AS ForeignKey_Name, object_schema_name(referenced_object_id) Parent_Schema_Name, object_name(referenced_object_id) Parent_Object_Name, object_schema_name(parent_object_id) Child_Schema_Name, object_name(parent_object_id) Child_Object_Name, STUFF((SELECT ', ' + c.name FROM sys.foreign_key_columns fkc JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id WHERE fkc.constraint_object_id = sys.foreign_keys.object_id ORDER BY constraint_column_id FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)') , 1, 2, '') AS ColumnList, is_disabled, is_not_trusted, 'ALTER TABLE ' + quotename(object_schema_name(parent_object_id)) + '.' + quotename(object_name(parent_object_id)) + ' NOCHECK CONSTRAINT ' + object_name(object_id) + '; ' AS Disable, 'ALTER TABLE ' + quotename(object_schema_name(parent_object_id)) + '.' + quotename(object_name(parent_object_id)) + ' WITH CHECK CHECK CONSTRAINT ' + object_name(object_id) + '; ' AS Enable FROM sys.foreign_keys -- Include this WHERE clause to pull the foreign keys for a single table (parent & child). -- WHERE parent_object_id = object_id('TableName') -- OR referenced_object_id = object_id('TableName')
Note: Even with a disabled foreign key you still won’t be able to truncate the table. Also remember that truncate is not the same as deleting everything (resets identity column for example).
Edit: Updated query to include referenced column list
Category: Microsoft SQL Server, Referential Integrity, SQLServerPedia Syndication, T-SQL | Tags: microsoft sql server, Referential Integrity, T-SQL
3 thoughts on “Finding, disabling and enabling foreign keys”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
I just want to point out the oft misunderstood re-enabling syntax, which will leave your FKs un-trusted, which as Kenneth points out will not be considered by the Query Optimiser. It looks odd and maybe that’s why but you need a CHECK to re-enable the FK and a WITH CHECK to check that all rows conform and hence the FK can be trusted.
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
WITH CHECK CHECK CONSTRAINT constraint_name
I have to admit that one throws me every now and again too.
[…] key is frequently used in related tables to tie the two tables together. Typically this is a Foreign Key […]