Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: What is the best way to add new field to an existing Primary Key

    Hi All,
    I have a situation which need to add a new field to an existing primary key. The table rows is around 3 millions. The only way I know to do this is

    - drop primary key
    - re create the primary key with adding new field

    but my concern is that the rows contain 3 millions rows. This table get updated frequently, so I may need to cease user from inserting into this table temporarily. Is there a way to estimate how long it will take to perform this changes ? Or is there a better way ?

    Rgrds
    Ezree

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >- drop primary key
    >- re create the primary key with adding new field

    FWIW - databases do not have "fields"
    Does new column exist in table now?
    Is new column populated now?
    if/when the answers to both questions are "YES",
    then you can add new UNIQUE INDEX covering old PK columns plus the new column
    All of the above won't impact existing users or application.
    Is current PK a FK to any table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by anacedent View Post
    FWIW - databases do not have "fields"
    Not to high jack this thread but do you insinuate that tables have fields / records and not databases? I thought fields or records as they're sometimes labeled are just the columns specified in a table, no?

  4. #4
    Join Date
    Aug 2006
    Posts
    3
    YES - column exist and new column is actually old column , because of unique constraint issue it is now decided to add new field to an existing composite primary key (of 3 columns)

    If we add new UNIQUE INDEX (old PK + new colum) i'm afraid it will only pass for UNIQUE INDEX, but will still fail at PK level.

    PK = ColA + ColB + ColC
    UNIQUE INDEX = PK + ColD

    See example data below:
    ColA ColB ColC ColD ColE
    1 2 3 1 1 ---> insert this will pass
    1 2 3 4 1 ---> insert this will pass for UI, but fail at PK

    Fyi current PK is a standalone, I meant not reference / reference to any table

    Is my understanding correct ?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Two goals need to be met
    1) don't break existing application
    2) minimize down time for existing application

    After new UNIQUE INDEX (UI) exists, then you can drop old PK the make new PK that uses the UI
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2006
    Posts
    3
    Ok that sound logical, but how can we estimate how long does it take

    i) to create UI on existing 3 millions of records on that table ?
    ii) and how long does it takes drop PK on the same table ?

    coz if both take longer time to perform, to prevent "insert this will pass for UI, but fail at PK" there must be some down time, but how long i'm not sure, needs to plan properly.

    Waiting for ur feedback. Thanks Anacedent

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >coz if both take longer time to perform, to prevent "insert this will pass for UI, but fail at PK"
    Above is true, BUT this is EXACT same behavior as exists right now!
    application behaves exactly the same as it did yesterday with the UI in place.
    the time to DROP the PK is instantaneous.
    after the old PK has been removed, new duplicates are avoided by the UI
    Time to make new PK using the new UI will be quick since the index already exists
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •