If you’re getting SQL Server error 11415 that reads something like “Object ‘UQ_Employees_Email’ cannot be disabled or enabled. This action applies only to foreign key and check constraints“, it’s probably because you’re trying to disable either a DEFAULT, UNIQUE or PRIMARY KEY constraint.

These constraint types cannot be disabled. As Microsoft states:

DEFAULT, PRIMARY KEY, and UNIQUE constraints can’t be disabled.

However, it is possible to disable the index of UNIQUE and PRIMARY KEY constraints, which effectively disables the constraint. Otherwise you’ll need to drop them (as you’ll need to do for any DEFAULT constraints).

So to fix this error, either disable the constraint’s index (depending on its type) or drop it altogether.

Example of Error

Here’s an example of code that produces the error:

ALTER TABLE dbo.Employees NOCHECK CONSTRAINT PK__Employee__7AD04FF1A39ECAB1;

Output:

Msg 11415, Level 16, State 1, Line 1
Object 'PK__Employee__7AD04FF1A39ECAB1' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

In this case I tried to disable a PRIMARY KEY constraint.

Here’s one regarding a UNIQUE constraint on the same table:

ALTER TABLE dbo.Employees NOCHECK CONSTRAINT UQ_Employees_Email;

Output:

Msg 11415, Level 16, State 1, Line 1
Object 'UQ_Employees_Email' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

And here’s one regarding a DEFAULT constraint on the same table:

ALTER TABLE dbo.Employees NOCHECK CONSTRAINT DF_Employees_Email;

Output:

Msg 11415, Level 16, State 1, Line 1
Object 'DF_Employees_Email' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

All three times we get the same error, which tells us that we can’t disable these constraint types.

Solution 1: Disable the Index

If we’re trying to disable a UNIQUE or PRIMARY KEY constraint, we can disable its index.

Here’s an example of disabling the PRIMARY KEY‘s index:

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.
Total execution time: 00:00:00.009

This warns us that a foreign key was disabled as a result of disabling the primary key.

It also warns us that another index on the same table was disabled as a result. This other index just so happens to be the UNIQUE index that we were trying to disable above, so we’ve disabled both indexes in one go.

But it doesn’t work the other way around. Had we disabled the UNIQUE index first, the PRIMARY KEY index would still be enabled (and so would the foreign key in the other table). So if we wanted to disable both, we would’ve had to run two ALTER INDEX statements; one for the UNIQUE constraint, and one for the PRIMARY KEY constraint.

Disabling a UNIQUE index uses exactly the same syntax, so we could use the same code but use the UNIQUE index’s name instead.

As for the DEFAULT constraint, we’ll need to drop it.

Solution 2: Drop the Constraint

Another option is to drop the constraint altogether. As for the DEFAULT constraint, this is our only option.

Let’s drop the DEFAULT constraint:

ALTER TABLE Employees DROP CONSTRAINT DF_Employees_Email;

That drops the constraint altogether.

We can add the constraint again in the future by using the ADD CONSTRAINT clause of the ALTER TABLE statement:

ALTER TABLE dbo.Employees
ADD CONSTRAINT DF_Employees_Email DEFAULT '[email protected]' FOR Email;

As for the primary key, if we drop a primary key that’s referenced by any foreign keys, we’ll need to drop those foreign keys first.