If you’ve got a table that has a UNIQUE constraint, there may come a time where you need to remove it. Whether it’s a permanent thing or temporary, you can easily delete the constraint using the following method.

Drop the Constraint

If you want to delete the constraint while keeping the table, you can use the ALTER TABLE statement, along with the DROP CONSTRAINT argument.

Example:

ALTER TABLE Employees
DROP CONSTRAINT UQ_Employees_Email;

That drops a UNIQUE constraint called UQ_Employees_Email from a table called Employees. The table will remain after this code has been run.

You can use the IF EXISTS clause if you don’t want to get an error in the event that it doesn’t exist:

ALTER TABLE Employees
DROP CONSTRAINT IF EXISTS UQ_Employees_Email;

The above code examples assume you know what the name of the constraint is. If you don’t know the constraint name, you can use the following code to retrieve it:

SELECT name  
FROM sys.key_constraints
WHERE type = 'UQ'
AND OBJECT_NAME(parent_object_id) = 'Employees';

Just swap Employees for the name of your table.

You can also swap sys.key_constraints for sys.objects if you prefer.

Delete a UNIQUE Index

There may be times where you think you want to delete a UNIQUE constraint, but you actually need to drop a UNIQUE index. This will be the case if a UNIQUE index was implemented in place of a UNIQUE constraint.

This could’ve been done in order to implement a filtered UNIQUE index (so that uniqueness is enforced on only some of the rows). Either way, if you want to get rid of it, you’ll need to use the DROP INDEX statement:

DROP INDEX UX_Employees_Email ON Employees;

Here I dropped an index called UX_Employees_Email.

You may be thinking that you could use DROP INDEX to drop the UNIQUE constraint too, seeing as SQL Server enforces uniqueness via a UNIQUE index. But you’d be wrong. The SQL Server documentation explicitly states that:

The DROP INDEX statement doesn’t apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.

And if we try to use DROP INDEX to drop a UNIQUE constraint then we’ll end up with an error that looks like this:

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.

So for UNIQUE constraints (and PRIMARY KEY constraints), we need to stick to the ALTER TABLE statement method shown above.

As for finding the name of indexes on a given table, we can do something like this:

SELECT * 
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Employees');

That will return all indexes for the table. You can check the filter_definition column to see if any of them are implemented as a filtered UNIQUE index. Such indexes will have 0 in the is_unique_constraint column. That column will be 1 if the index was implemented as an actual UNIQUE constraint.

Drop the Table

Another way to delete a UNIQUE constraint is to drop the table it belongs to. Obviously this is only an option if you want to drop the table. But the takeaway is that we don’t need to drop the UNIQUE constraint before dropping the table. Basically, as reported in the SQL Server documentation, the DROP TABLE statement removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

So we could do something like this:

DROP TABLE Employees;

That will drop the table, as well as our UNIQUE constraint and any UNIQUE indexes we have applied to it.

Advanced Options

The ALTER TABLE syntax allows for various options applicable only to “clustered constraints”. If this applies to your situation, check out the official documentation for more information.