Error trying to remove index

Question

I'm new in SQL and I'm practicing some exercises using Microsoft SQL Server. I created this index and then I'm trying to drop the index but it says that it is not a constraint. But custID column SHOWS THAT IT is the PRIMARY KEY.

This is what I did:

CREATE INDEX indexcustInfoID ON tblCustomerIDInfo (CustID) 

ALTER TABLE tblCustomerIDInfo 
DROP CONSTRAINT indexcustInfoID  

Error:

Msg 3728, Level 16, State 1, Line 26
'indexcustInfoID' is not a constraint.

Msg 3727, Level 16, State 0, Line 26
Could not drop constraint. See previous errors.

Please help me!

Answer 1

You want DROP INDEX instead of DROP CONSTRAINT. DROP CONSTRAINT only applies to indexes created for primary keys and UNIQUE constraints. It sounds like you've added another index on the primary key column, which is not the primary key constraint itself.

From MSDN:

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.