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.
- 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 databaseOnce 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.
- 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
- 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 tabledbcc
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:
- Disable foreign key constraints
- Drop indexes on fact tables
- Import data
- Recreate indexes on fact tables
- Re-enable constraints with the WITH CHECK option
sp_MSforeachtable
‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL’ — enables all constraints in the database
