CODEWITHSUNDEEP
databaseoracle-databasesurrogate-key
simon
simon

Reputation: 12902

Preferred way to add surrogate key to existing Oracle DB table

I have to modify an existing table in a Oracle 10g DB with a few thousand records to add a surrogate autonumber key. One way that comes to my mind is to

  1. Create a new sequence
  2. Create the id column, allowing null values
  3. Updating the id column with the sequence
  4. Alter table to add "not null" and "primary key" for the new id column

Is there an easier or more efficient way of doing this (or is there some reason why this wouldn't work)?

Upvotes: 8

Views: 6137

Answers (1)

Quassnoi
Quassnoi

Reputation: 425371

I'd do it the following way:

  1. Create the id column, allowing null values

  2. Issue this query:

    UPDATE  mytable
SET     id = rownum

  3. Alter table to add NOT NULL and PRIMARY KEY for the new id column

  4. Create the sequence, seeding it to MAX(id) + 1 and use it for the further inserts.

Upvotes: 8

Related Questions