Monday, April 22, 2019

How to disable or re-enable table constraints

Say you are copying data to one table from another, and you need to work around a  particular FOREIGN KEY constraint.  Or, maybe you you need to work around ALL FK constraints on that table.  We'll often need to work around FK constraints in situations like these.  This post is just a quick how-to, for a single table constraint, or all constraints for a table.


For all constraints on a table, replace 'TableName' with your table name:

       -- Disable all table constraints, whole database
       ALTER TABLE TableName NOCHECK CONSTRAINT ALL

       ------ do your table insert here

       -- Enable all table constraints, whole database
       ALTER TABLE TableName CHECK CONSTRAINT ALL 


For a single constraint on a table, replace 'TableName' and 'ConstraintName' with your specific object names:

       -- Disable single constraint, single table
       ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

       ----- do your table insert here

       -- Enable single constraint, single table
       ALTER TABLE TableName CHECK CONSTRAINT ConstraintName



There you have it.

1 comment:

  1. Actually you when re-enabling the check constraint you will need one of my favourite TSQL syntaxes - WITH CHECK CHECK CONSTRAINT - if you want to mark the constraint as trusted so the optimizer can use it.

    see

    https://dba.stackexchange.com/questions/167861/what-is-a-with-check-check-constraint

    ReplyDelete