New primary key.

Posted on 2011-04-28
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


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

    Assisted Solution

    existing data has to conform to the uniqueness of the new primary key
    LVL 9

    Assisted Solution

    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.
    LVL 6

    Assisted Solution

    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
    Also make sure value in foreign key is valid.
    LVL 15

    Assisted Solution

    by:Aaron Shilo

    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


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now