Can't Delete Index b/c of Constraint

  • SwedishOrr

    SSCrazy

    Points: 2955

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • SwedishOrr

    SSCrazy

    Points: 2955

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • SwedishOrr

    SSCrazy

    Points: 2955

    sp_helpindex:

    index_name index_description

    _dta_index_NAMES_6_1110295015__K1_K4_2 nonclustered located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_2 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_6 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_K2 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_K2_3_4_5_6_7 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_K3 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K1_K4 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K2_1 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K2_1_3_4_5_6_7 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K2_K1 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K2_K1_3_4_5_6_7 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K3_K1 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K4 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K4_K1 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K4_K1_2 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K5 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K5_K1 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_8_1110295015__K5_K2 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K1 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K1_K2 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K1_K4 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K2 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K2_K1 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K3 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K4 nonclustered, hypothetical located on PRIMARY

    _dta_index_NAMES_c_8_1110295015__K4_K1 nonclustered, hypothetical located on PRIMARY

    CIX_NAMES_NAMEID clustered, unique located on PRIMARY

    PK__NAMES__NAMEID nonclustered, 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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • SwedishOrr

    SSCrazy

    Points: 2955

    Query 1:

    DF_NAMES_UPDATE_DATE DEFAULT_CONSTRAINT

    PK__NAMES__NAMEID PRIMARY_KEY_CONSTRAINT

    NAMES USER_TABLE

    Query 2:

    FK__STATUS_NAMES__NAMES STATUS_NAMES NAMES

    FK_SOURCE_NAMES_NAMES SOURCE_NAMES NAMES

    FK_LIST_NAMES_NAMES LIST_NAMES NAMES

    FK__ADDRESS__NAME_1 ADDRESS NAMES

    FK__DEMOGRAPH__NAME___2EA5EC27 DEMOGRAPHIC NAMES

    FK_PHONE_NAMES PHONE NAMES

    FK_hi_category_names_NAMES hi_category_names NAMES

    FK_category_names_NAMES category_names NAMES

    FK1_SOURCE_NAMES_NAMES temp_SOURCE_NAMES NAMES

    Thanks,

    Nate

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • SwedishOrr

    SSCrazy

    Points: 2955

    Okay sounds good. Is there a way to specify what the FKs reference explicitly?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • SwedishOrr

    SSCrazy

    Points: 2955

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • SwedishOrr

    SSCrazy

    Points: 2955

    Makes sense, thanks!

  • imSQrLy

    Ten Centuries

    Points: 1335

    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 14 (of 14 total)

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