Change primary key from nonclustered to clustered, how?

Posted on 2004-11-01
Last Modified: 2012-08-13
CREATE TABLE dbo.sometable
    col1       numeric(9,0) NOT NULL,
    col2        numeric(9,0) NOT NULL,
    col3   datetime     NOT NULL,
    col4 varchar(10)  NOT NULL,
    col5  char(1)      NULL,
    col6   datetime     NULL,
    CONSTRAINT sometable_15506255362
   PRIMARY KEY NONCLUSTERED (col1,col2,col3,col4)

i just want to change this primary key to clustered, how do i do that? i searched the entire transact sql guide there are tons examples on how to add/drop indexes, but never alter indexes/primary key.

1)i cant add/drop, because it will in term drop all the other foreign key constraints
2)i dont want to drop/create the whole table

thanks for your help!
Question by:gagaliya
    LVL 14

    Expert Comment

    Drop and recreate constraint:

    1. drop
    ALTER TABLE dbo.sometable DROP CONSTRAINT sometable_15506255362

    2. recreate
    ALTER TABLE dbo.sometable ADD CONSTRAINT sometable_15506255362 PRIMARY KEY CLUSTERED (col1,col2,col3,col4)
    LVL 1

    Author Comment

    jan franek,

    i cant add/drop, because it will in term drop all the other foreign key constraints
    LVL 14

    Expert Comment

    AFAIK droping one constraint keeps other constraints unaffected. I have tried to create table with primary key and foreign key, then I have droped primary key and foreign key still existed. Why do you think, that droping primary key will affect other constraints ?
    LVL 1

    Author Comment

    hey jan, because when i try to drop the primary key, dbartisan told me: are you sure? all foreign keys referencing this primary key will be deleted. This cannot happen.  
    LVL 14

    Accepted Solution

    Yes, I see, you're right.

    I'm afraid, that if you really want to change your primary key to clustered, you have to drop it (and also drop all referencing foreign keys from other tables) and recreate it (and recreate again that foreign keys).

    You can get list of existing foreign keys referencing your primaty key using sp_helpconstraint - I believe, that with little tweaking you can generate script for droping and recreating all foreign keys.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    This article describes how to create custom column layout styles for Bootstrap. The article uses 5 columns to illustrate the concept, but the principle can be extended to any number of columns.
    School is back in session! The beginning of the school year is a fresh slate. One way for students to get started on the right foot is to get organized.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now