Disable and enable Foreign Key and Check Constraints

Foreign Key and Check Constraints can be disabled or enabled when required.

To disable a constraint use this code:

USE Database_name
GO
ALTER TABLE Table_name
NOCHECK CONSTRAINT Constraint_name
GO

To disable all table constraints:

USE Database_name
GO
ALTER TABLE Table_name
NOCHECK CONSTRAINT ALL
GO

To enable a disabled constraint with checking the existing rows use this code:

USE Database_name
GO
ALTER TABLE Table_name
WITH CHECK CHECK CONSTRAINT Constraint_name
GO

To enable all disabled table constraints with checking the existing rows:

USE Database_name
GO
ALTER TABLE Table_name
WITH CHECK CHECK CONSTRAINT ALL
GO

If you enable a constraint without checking the existing rows (WITH NOCHECK), SQL Server will mark the constraint as “not trusted”. Trusted constraints help optimizer to get optimized plan and performance.
To see if you have untrusted constraints in your database run:

SELECT *
from sys.check_constraints
WHERE is_not_trusted = 1

SELECT *
from sys.foreign_keys
WHERE is_not_trusted = 1

To list all Check constraints in a database run:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'CHECK'

To see what Check constraints are disabled run:
SELECT name, is_disabled
FROM sys.check_constraints

To list all Foreign key constraints in a database run:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

To see what Foreign keys are disabled run:
SELECT name, is_disabled
FROM sys.foreign_keys