Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Change primary key from nonclustered to clustered, how?

Posted on 2004-11-01
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 14

Expert Comment

by:Jan Franek
ID: 12467571
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)

Author Comment

ID: 12468514
jan franek,

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

Expert Comment

by:Jan Franek
ID: 12468656
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 ?

Author Comment

ID: 12472927
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

Jan Franek earned 750 total points
ID: 12473533
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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With the evolution of technology, we have finally reached a point where it is possible to have home automation features like having your thermostat turn up and door lock itself when you leave, as well as a complete home security system. This is a st…
An overview of cyber security, cyber crime, and personal protection against hackers. Includes a brief summary of the Equifax breach and why everyone should be aware of it. Other subjects include: how cyber security has failed to advance with technol…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

610 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