How to Disable a Constraint in SQL Server

Posted on by Ian

Disabling constraints in SQL Server can be useful for various operations, such as bulk data loading or certain maintenance tasks. But not all constraints are created equal. The method we use to disable a constraint, and whether that’s even possible, depends on the type of constraint.

In this article, we’ll explore how to disable different types of constraints and consider the potential impacts.

Disabling FOREIGN KEY and CHECK Constraints

To disable a FOREIGN KEY or CHECK constraint, we can use the ALTER TABLE statement with the NOCHECK option. Here’s the syntax:

ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;

For example, to disable a foreign key constraint named FK_Jobs_Employees on the Jobs table:

ALTER TABLE Jobs
NOCHECK CONSTRAINT FK_Jobs_Employees;

To disable all constraints on a table:

ALTER TABLE table_name
NOCHECK CONSTRAINT ALL;

See the following articles for more details and examples:

Disabling DEFAULT Constraints

DEFAULT constraints cannot be disabled directly. Instead, we need to drop and recreate them. Here’s how:

  1. First, script the existing DEFAULT constraint (replacing table_name with your table name):
SELECT
    name,
    definition
FROM 
    sys.default_constraints
WHERE 
    parent_object_id = OBJECT_ID('table_name');
  1. Drop the constraint:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
  1. When it’s time to re-enable the constraint, recreate it using the script from step 1.

Disabling PRIMARY KEY and UNIQUE Constraints

Similar to DEFAULT constraints, PRIMARY KEY and UNIQUE constraints cannot be disabled. However, we can disable their index. When we create one of these constraint types, an index is automatically created for the constraint. By disabling the index, we effectively disable the constraint.

Here’s how to disable the index:

ALTER INDEX PK_index_name ON table_name DISABLE;

Replace PK_index_name with the name of your constraint/index, and table_name with the name of your table.

You can find all indexes for a table with the following query:

SELECT
    name,
    type_desc,
    is_primary_key,
    is_unique_constraint,
    is_disabled
FROM 
    sys.indexes
WHERE 
    object_id = OBJECT_ID('table_name');

Replacing table_name with the name of your table.

You can also disable all indexes in one go by replacing the index name with the ALL keyword:

ALTER INDEX ALL ON table_name DISABLE;

So to recap, these disable the index for the constraints.

We can re-enable these constraints by changing DISABLE to REBUILD. For example, to rebuild all indexes on a table:

ALTER INDEX ALL ON table_name DISABLE;

Another way of dealing with these constraints is to drop them and recreate them later when you need to re-enable them.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Impact on Related Foreign Keys and Unclustered Indexes

When disabling a PRIMARY KEY or UNIQUE constraint, we need to consider any FOREIGN KEY constraints that reference it.

Also, disabling a clustered index automatically disables any unclustered indexes on the table too.

For example, here’s what happens when I disable a (clustered) primary key on a table that also has an unclustered index, as well as a foreign key that references it from another table:

ALTER INDEX PK__Employee__7AD04FF1A39ECAB1 ON Employees DISABLE;

Output:

Warning: Foreign key 'FK_Jobs_Employees' on table 'Jobs' referencing table 'Employees' was disabled as a result of disabling the index 'PK__Employee__7AD04FF1A39ECAB1'.
Warning: Index 'UQ_Employees_Email' on table 'Employees' was disabled as a result of disabling the clustered index on the table.

So while we need to be mindful of this, it’s also an efficient way of disabling everything at once, if that’s what we want to do.

As for dropping the constraint itself, SQL Server will not allow us to drop a constraint that is referenced by a foreign key.

To handle this:

  1. Drop all foreign key constraints that reference the primary key or unique constraint we want to drop.
  2. Drop the primary key or unique constraint.
  3. Perform the required operations.
  4. Recreate the primary key or unique constraint.
  5. Recreate the foreign key constraints.

Remember to always re-enable or recreate constraints after your operations to maintain data integrity.