Change primary key from nonclustered to clustered, how?

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!
Who is Participating?
Jan FranekCommented:
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.
Jan FranekCommented:
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)
gagaliyaAuthor Commented:
jan franek,

i cant add/drop, because it will in term drop all the other foreign key constraints
Jan FranekCommented:
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 ?
gagaliyaAuthor Commented:
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.  
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.