Disable Constraint in SQL Server

In my previous articles we did talk more about different constraints in SQL Server. But the topic or the point which I didn’t cover was “How to Disable Constraints”.

The only reason which I can think of interims of disabling constraint is during heavy data loads. By disabling constraint certainly improves the performance but taking a BIG risk at the data quality. I don’t recommend disabling the constraints, especially in OLTP systems.

As you know there are 5 constraints in SQL Server they are:
1) Primary Key
2) Foreign Key
3) Unique Key
4) Check
5) DEFAULT

We can’t disable Primary Key, Unique Key and DEFAULT constraints. So the only constraints which can be disabled are Foreign Key and CHECK Constraint.

The basic syntax is:

ALTER TABLE TABLE_NAME { NOCHECK CHECK } CONSTRAINT CONSTRAINT_NAME;

NOCHECK is used to disable and CHECK is used to enable the constraint.

Disable Foreign Key Constraint:
ALTER TABLE EMPLOYEE NOCHECK CONSTRAINT FK_EMPLOYEE_01

Enable Foreign Key Constraint:
ALTER TABLE EMPLOYEE CHECK CONSTRAINT FK_EMPLOYEE_01

Script to find Foreign Key Constraints State:
SELECT (CASE
WHEN IS_DISABLED = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
NAME AS CONSTRAINT_NAME,
OBJECT_NAME(PARENT_OBJECT_ID) AS TABLE_NAME
FROM SYS.foreign_keys

Disable Check Constraint:
ALTER TABLE ACCOUNT NOCHECK CONSTRAINT cc_MinBal

Enable Check Constraint:
ALTER TABLE ACCOUNT CHECK CONSTRAINT cc_MinBal

Script to find Check Constraints State:
SELECT (CASE
WHEN IS_DISABLED = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
NAME AS CONSTRAINT_NAME,
OBJECT_NAME(PARENT_OBJECT_ID) AS TABLE_NAME
FROM SYS.check_constraints