New primary key.

Posted on 2011-04-28
Medium Priority
Last Modified: 2012-05-11
I need to change the primary key to a different column.

The table is small with about 10 rows of  data.

The current primary key is used as a forgien key in 3 other tables.

The new primary key will become the new forgien key.

Are there any things which I need to be aware of. Is it as easy as:
1. Drop forgien key constaints.
2. Drop primary key constraint.
3. Create new primary key.
4. Create new forgien keys.
Question by:Mr_Shaw
LVL 32

Accepted Solution

Ephraim Wangoya earned 336 total points
ID: 35484331

You have the steps correct, I don't see any issues
LVL 18

Assisted Solution

deighton earned 336 total points
ID: 35484424
existing data has to conform to the uniqueness of the new primary key

Assisted Solution

radcaesar earned 332 total points
ID: 35485048
The only thing you need to be aware is the new primary key field which you are going to set will only have unique values without any NULL.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Assisted Solution

LCSandman8301 earned 332 total points
ID: 35485217
a step you may want to try before you do this and get yourself into a bind is to add a unique nonclustered index to the new field first. it may save you a bit of a headache.
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 332 total points
ID: 35489514
Also make sure value in foreign key is valid.
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 332 total points
ID: 35489959

i agree with ewangoya
i see no issue with your way , just make sure that the column has all the reference values and your a go

Author Closing Comment

ID: 35511258

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

850 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