Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005

    Unanswered: Problem dropping an index with a unique constraint

    Quick question for SQL Server 2000:

    I've got an index which I want to drop. But when I execute the DROP INDEX command I get the following error:

    "An explicit DROP INDEX is not allowed on index 'tbl_Payroll_SubType.IX_tbl_Payroll_SubType'. It is being used for UNIQUE KEY constraint enforcement."

    I've tried to go into Enterprise Manager and remove the constraint, but it won't let me do that either. How do I get rid of this index?

    Thanks in advance for your help!

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    If it is a UNIQUE KEY, you will need to remove it using the ALTER TABLE statement.
    alter table tbl_Payroll_SubType drop constraint IX_tbl_Payroll_SubType
    But you may want to keep a way of getting that constraint back, as I am sure it was put in for a reason. If there are any foreign keys hanging off of this UNIQUE KEY constraint, those relationships will also need to be dropped.

  3. #3
    Join Date
    Jun 2005
    Thanks so much! That did it. I was searching around for the constraint name, thinking that it would be called something different from the index.

    The index and constraint were put in because the people who created the database didn't know what they were doing. There is already a primary key defined for the table and this index contains the primary key along with another column (which is also defined in another index).

    Thanks again for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts