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:
- STEP 1 - Drop the existing composite primary key. Check if the unique index is also dropped.
- STEP 2 - Add the new column which will eventually be the surrogate key
- 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?
- STEP 4 - Introduce primary key constraint on the surrogate key column
Kindly review and offer your suggestions.