Finding, disabling and enabling foreign keys

3

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,
	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).

3 thoughts on “Finding, disabling and enabling foreign keys

  1. Michael says:

    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

  2. […] key is frequently used in related tables to tie the two tables together. Typically this is a Foreign Key […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: