Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert Primary Key - From Composite to Surrogate - Recommendations

Srini MadJan 25 2018 — edited Jan 30 2018

Oracle DB Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

We are having a table where the primary key today is a composite of 4 columns. We are having many child tables being added as part of the next release and see benefit in introducing a surrogate key as a primary key.

I was searching around for any recommendations on how to update this surrogate key column on existing records in the table and could not find any. I had put down below steps for my upgrade:

  1. STEP 1 - Drop the existing composite primary key. Check if the unique index is also dropped.
  2. STEP 2 - Add the new column which will eventually be the surrogate key
  3. STEP 3 - Update the existing records for this newly added surrogate key - This is an area of confusion as we have 3 lakh records to be updated. I was thinking of using a PL/SQL anonymous block to update the column using a DB sequence - but can this be done using simple SQL or any other better ways?
  4. STEP 4 - Introduce primary key constraint on the surrogate key column

Kindly review and offer your suggestions.

This post has been answered by Paulzip on Jan 25 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 26 2018
Added on Jan 25 2018
25 comments
2,256 views