An Explicit DROP INDEX is Not Allowed On the Index, Msg 3723

by

Great title, huh?

Anyway, I came across this error last week so I thought I would do a quick post in case anyone else comes across this same issue when trying to drop an index. The error message is very clear for this:

--Msg 3723, Level 16, State 4, Line 1
--An explicit DROP INDEX is not allowed on index 'Table.PK__Table__3214EC277BB05806'.
--It is being used for PRIMARY KEY constraint enforcement.

 

OK then, we first need to remove the constraint:

ALTER TABLE [Table] 
DROP CONSTRAINT [PK__Table__3214EC277BB05806]
GO

 

And the index, along with the primary key (and that error message), will go away.

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.”

So, there you go. You’ll thank me later.

8 thoughts on “An Explicit DROP INDEX is Not Allowed On the Index, Msg 3723”

    • Um, I suppose you might be able to do so, but the real question I have is why would you need to drop an index against that table?

      Reply

      • Various reasons…say you need to change the length of a field being used as a primary key – you have to drop the index first, then change the field, then recreate the constraint. If you think about it, there are many reasons one would need to do something like this from time to time.

        Reply

        • Yes, I understand the needs for altering table schemas from time to time.

          However, what the OP (Ajay) was asking about here is the SYSUSERINFO table, which I believe is a system table for MS Dynamics. That is what led to my comment asking why they would need to make alterations against that table, because I wanted to know more about what they were trying to accomplish with Dynamics.

          If you can provide a use case for altering this table in Dynamics, let me know! I’m always happy to learn new things.

          Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.