Post reply

Adding a cluster index to pk that has a unclustered one that is referenced by 10 fk

  • February 7, 2012 at 7:30 am

    #391493

    I have a pk that has a uncluster index and is referenced by other 10 fk in 10 other tables if I added a cluster one to pk will the foreign keys reference it automatically then I can delete the unclustered one or things will get broken or how can I do that

  • February 7, 2012 at 7:37 am

    #1443776

    No, the foreign keys will not automatically reference it. If you add a clustered index on a column that is already the PK, the only thing that happens is that a clustered index gets created in addition to the existing index.

    If you want the pk to be clustered, you need to drop all foreign keys, drop the pk, recreate the pk clustered and recreate all the foreign keys.

    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

  • February 7, 2012 at 11:02 am

    #1443990

    Thanks a lot

    is there a script to drop all fk referencing a particular pk in all tables

    and another script to recreate all fk referencing a particular pk

    since I have around 15 tables have fk referencing that pk.

    Thanks again

  • February 7, 2012 at 12:00 pm

    #1444033

    I'm sure there is, have you checked the script library here?

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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