Can't Delete Index b/c of Constraint

  • Hi All,

    I've got a table that has a primary key, an index on that primary key, and an additional clustered index. I need to drop the clustered index but can't because it's enforcing foreign key constraints. How can I force SQL server 2005 to use the primary key for that? I can't seem to find anything on the net. Any help is GREATLY appreciated. :w00t:

    Thanks,

    Nate

  • The primary key is enforced by an index and, I'd guess from your description that the primary key is the clustered index (use sp_helpindex to check). You can't drop the index that enforces a constraint without dropping the constraint itself and, with the primary key, that means dropping the foreign keys as well.

    You should be able to drop the nonclustered index without problems. If you get problems, then post the output of helpindex on that table.

    What are you trying to achieve?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail, the quick response is much appreciated. Also, nice presentation at PASS 2008.

    The primary key is actually non-clustered, and I'm trying to delete a clustered index that's on the same column as the PK. I tried dropping the foreign key constraints which I found using:

    select *

    from sys.sysobjects

    where xtype = 'F'

    But I apparently didn't get them all because I was still not able to drop either index. FKs aren't enforced across databases are they?

    I created the clustered index for testing purposes but found that it causes the CPU usage to soar, so I'm trying to drop it.

    Hope that helps.

    Thanks again,

    Nate

  • nate (11/26/2008)


    Gail, the quick response is much appreciated. Also, nice presentation at PASS 2008.

    Oh, thanks.

    The primary key is actually non-clustered, and I'm trying to delete a clustered index that's on the same column as the PK.

    Foreign keys are enforced from the pk or from a unique constraint, not from plain indexes.

    What's the output of sp_helpindex on that table?

    What's the exact error you get if you try to run a drop index on that clustered index

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sp_helpindex:

    index_name index_description

    _dta_index_NAMES_6_1110295015__K1_K4_2nonclustered located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_2nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_6nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_K2nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_K2_3_4_5_6_7nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_K3nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_K4nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K2_1nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K2_1_3_4_5_6_7nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K2_K1nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K2_K1_3_4_5_6_7nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K3_K1nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K4nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K4_K1nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K4_K1_2nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K5nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K5_K1nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K5_K2nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K1nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K1_K2nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K1_K4nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K2nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K2_K1nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K3nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K4nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K4_K1nonclustered, hypothetical located on PRIMARY

    CIX_NAMES_NAMEIDclustered, unique located on PRIMARY

    PK__NAMES__NAMEIDnonclustered, unique, primary key located on PRIMARY

    error:

    Msg 3723, Level 16, State 6, Line 3

    An explicit DROP INDEX is not allowed on index 'dbo.NAMES.CIX_NAMES_NAMEID'. It is being used for FOREIGN KEY constraint enforcement.

    Thanks,

    Nate

  • Hmmm. Odd. Did you create that through the management studio GUI? If so, what options did you select?

    What do the following return?

    select name, type_desc from sys.objects

    where object_id = OBJECT_ID('NAMES') OR parent_object_id = OBJECT_ID('NAMES')

    select name, OBJECT_NAME(parent_object_id), OBJECT_NAME(referenced_object_id) from sys.foreign_keys

    where referenced_object_id = OBJECT_ID('NAMES')

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Query 1:

    DF_NAMES_UPDATE_DATEDEFAULT_CONSTRAINT

    PK__NAMES__NAMEID PRIMARY_KEY_CONSTRAINT

    NAMES USER_TABLE

    Query 2:

    FK__STATUS_NAMES__NAMES STATUS_NAMESNAMES

    FK_SOURCE_NAMES_NAMES SOURCE_NAMESNAMES

    FK_LIST_NAMES_NAMES LIST_NAMESNAMES

    FK__ADDRESS__NAME_1 ADDRESSNAMES

    FK__DEMOGRAPH__NAME___2EA5EC27DEMOGRAPHICNAMES

    FK_PHONE_NAMES PHONENAMES

    FK_hi_category_names_NAMES hi_category_namesNAMES

    FK_category_names_NAMES category_namesNAMES

    FK1_SOURCE_NAMES_NAMES temp_SOURCE_NAMESNAMES

    Thanks,

    Nate

  • All of the foreign keys that the query return reference the Names table. Try dropping them.

    It's odd, they should be referencing the pk, not the unique index.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Okay sounds good. Is there a way to specify what the FKs reference explicitly?

  • No. Just try not to have multiple constraints/unique indexes on the column(s) referenced by the key.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail,

    This did the trick nicely, thank you. However, I'm seeing that my processors are tracking extremely high now. Any idea why this would be happening?

    Thanks,

    Nate

  • It could be anything. That's almost the database equivalent of asking "Why's my car making a strange noise?"

    Probably you have some badly performing queries. Run profiler for a while and see what has high values for CPU and see if you can tune those queries. It may also be excessive compiles as by changing the indexes on that table, all query plans that use that table are now invalid and will have to be recompiled when the queries run. If that's the case, the cpu usage will drop off.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Makes sense, thanks!

  • Gail,

    I ran into this. I don't need help resolving, but I did want to know if you found where the behavior is documented. This is my scenario.

    Create table called main with a primary key column called MainID with an identity 1,1 and other columns if you wish. Do not set the pk to clustered index but only as unique.

    create a table called SubMain with an FK to PK on main.

    Later create a unique clustered index on on MainID call it IDX_UQ_CL_MainID.

    Just to test go an delete it. To prove you can delete it.

    Then add it back. So it will be there for the next step.

    Drop your FK in SubMain

    add back your FK in SubMain

    Try to delete the IDX_UQ_CL_MainID. You will get the error in this article.

    It seems that SQL Server uses the best index at the time an FK is created. Best meaning if there is a unique cluster index on the PK field then use it, if not use the next best index.

    Do you know of any documentation or setting that controls this. During the ALTER table ADD FK, you can only specify the column not the index that is used so i see no way to control this.

    I can see how to detect it and write better schema change scripts, but not how to prevent it in the first place...Assuming you have to add the indexes in the order specified based on a preexisting schema, or if you just inherit the problem.

    Ill work on getting the above in code if you have any trouble reproducing.

    Jimmy

    "I'm still learning the things i thought i knew!"

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply