Disabling Foreign Key Constraints

Disabling check constraints, such as foreign key constraints, is often required when populating a data warehouse. For example, you might want to disable a check constraint to speed up loading of a fact table.

1. How do we disable check constraints in SQL Server?
   You can script each check constraint or you can use the undocumented sp_MSforeachtable function.

   ALTER
   TABLE [dbo].[FACT_ITEM_INVENTORY] NOCHECK
   CONSTRAINT [FK_DIM_DATE_FACT_ITEM_INVENTORY_Date_Id] — disables a specific contraint
   

   sp_MSforeachtable
   ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’ — disables all contraints in the database

   Once the foreign keys are disabled, you can run the ETL job or delete records from the referenced table without referential integrity checks. Note that you cannot truncate a table even if you have disabled the constraints due to the different way SQL Server logs the truncate operation. You must drop the constraints if you want to truncate a table. You can use sp_MSforeachtable to drop constraints as well although I personally prefer to disable them for a reason that will become obvious in a moment.

2. How do we enable check constraints?

The same way you disabled them but this time you use the CHECK predicate:

ALTER
TABLE [dbo].[FACT_ITEM_INVENTORY] CHECK
CONSTRAINT [FK_DIM_DATE_FACT_ITEM_INVENTORY_Date_Id] — enables a specific constraint

sp_MSforeachtable
‘ALTER TABLE ? CHECK CONSTRAINT ALL’ — enables all contraints in the database

1. Does SQL Server verify referential integrity for existing data when you re-enable check constraints?

   This is where the behavior differs between recreating and enabling constraints. If you drop and create the constraints, the server will check the data unless WITH NOCHECK is used.

   ALTER
   TABLE [dbo].[FACT_ITEM_INVENTORY] WITH
   NOCHECK
   ADD
   CONSTRAINT [FK_DIM_DATE_FACT_ITEM_INVENTORY_Date_Id] FOREIGN
   KEY([Date_Id]) REFERENCES [dbo].[DIM_DATE] ([Date_Id])

   The server doesn’t check existing data if you enable a previously disabled constraint. You can manually check foreign key violations by using this command:

   dbcc
   checkconstraints(FACT_ITEM_INVENTORY) –- checks a specific table

   dbcc
   checkconstraints — checks the entire database
   

The checkconstraints command will output all rows that violate constraints.

UPDATE 10/31/2011

Reader Ian pointed out that re-enabling foreign key constraints without checking data marks them as non-trusted. Consequently, the SQL Server optimizer may choose different execution plans for a certain range of queries. Tibor Karaszi explains this in more details in his blog Non-trusted constraints and performance. Paul White also mentions about a bug in this area. This makes me believe that enabling check constraints without checking data should be avoided. Therefore, to speed up fact table imports for large dataset consider:

1. Disable foreign key constraints
2. Drop indexes on fact tables
3. Import data
4. Recreate indexes on fact tables
5. Re-enable constraints with the WITH CHECK option

sp_MSforeachtable
‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL’ — enables all constraints in the database