If you’re getting SQL Server error 3723 that reads “An explicit DROP INDEX is not allowed on index…” it’s probably because you’re trying to drop a PRIMARY KEY or UNIQUE constraint by dropping its index.

Although SQL Server does create an index behind the scenes whenever we create a PRIMARY KEY or UNIQUE index, we can’t delete those constraints by dropping their indexes. We have to use the ALTER TABLE statement with the DROP CONSTRAINT argument instead.

So to fix this issue, use the ALTER TABLE statement to drop the constraint.

Example of Error

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

DROP INDEX UQ_Employees_Email ON Employees;

Output:

Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'Employees.UQ_Employees_Email'. It is being used for UNIQUE KEY constraint enforcement.

The error message is reasonably self-explanatory – we can’t drop the index because it’s being used to enforce a constraint.

Solution

We can overcome this issue by dropping the constraint directly:

ALTER TABLE Employees
DROP CONSTRAINT UQ_Employees_Email; 

Output:

Commands completed successfully.

This time we were successful; the UNIQUE constraint was dropped.

Obviously, we’ll only get this error if the index is being used to enforce a PRIMARY KEY or UNIQUE constraint. We can still use the DROP INDEX statement to drop other indexes (i.e. ones that aren’t being used to enforce a PRIMARY KEY or UNIQUE constraint).