Cannot drop an index that is being used for PK constraint enforcement (MS SQL Server 2008)


I am trying to execute the following SQL command:

DROP INDEX pk_name_PK ON [dbo].[TableName];

I am getting the following error:

It is being used for PRIMARY KEY constraint enforcement.

Please help! Thanks!

1 Answer

If you happen to notice the MSDN entry on DROP INDEX, you will see that this behavior is noted. In case you overlooked it, the relevant comment is this:

“The DROP INDEX statement does not 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.”

You sould run this code:

alter table [dbo].[TableName] drop constraint pk_name_PK

So, there you go.


Post Answer