New primary key.

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.
Who is Participating?
Ephraim WangoyaCommented:

You have the steps correct, I don't see any issues
existing data has to conform to the uniqueness of the new primary key
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
Alpesh PatelAssistant ConsultantCommented:
Also make sure value in foreign key is valid.
Aaron ShiloChief Database ArchitectCommented:

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
Mr_ShawAuthor Commented:
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.