15

I have some columns with no primary key and want to add a primary key column.

NAME    Age
-------------
Peter   45
Bob     25
John    56
Peter   45

Some collegues suggest to add a PK with a sequences and triggers: Add a auto increment primary key to existing table in oracle

This is nice, but my customers use a Database User with no rights to add sequences or triggers. I want to prevent to contact dozens of DBA administrators to alter user rights or to run my scripts.

This is my suggestion to add a PK with only an update statement: (I need help in Step 2)

Step 1: Create the ID column (I have DB rights for this)

ALTER TABLE PERSON ADD ID NUMBER(10,0);

Step 2: Question: Can I initialize the ID column with unique values based on the order of the rows or something else? How?

UPDATE PERSON SET ID = something-unique

Step 3: Add the primary key contraint afterwords: (I DB have rights for this)

ALTER TABLE PERSON ADD CONSTRAINT PK_ID PRIMARY KEY(ID);

Step 4: Afterwords: the primary key is managed and added by my application.

This will be the result:

ID(PK)  NAME    Age
---------------------
1       Peter   45
2       Bob     25
3       John    56
4       Peter   45

Thanks folks!

Community
  • 1
  • 1
Dimitri Dewaele
  • 10,311
  • 21
  • 80
  • 127
  • WW gives the method for populating the PK based on a sequential number, but in your question you say, "... based on the order of the rows". Do you have a defined order for the rows, such as a "created_at" timestamp? – David Aldridge Nov 06 '13 at 08:56
  • I have updated my text with a more clear question. Thanks WW! – Dimitri Dewaele Nov 06 '13 at 09:00

2 Answers2

30
Update person set id = rownum;
WW.
  • 23,793
  • 13
  • 94
  • 121
1

THis idea is very childish, but should work fine if your table doesnot have large amount of rows.

For step 2, run a for loop like:

declare
    i pls_integer :=1;
     begin
    for rec in (select name,age, rowid from table_name)
    loop
    update table_name set id = i 
    where 
     table_name.name=rec.name 
     and table_name.age=rec.age 
     and table_name.rowid = rec.rowid;
    i:=i+1;
    end loop;
end;
A Nice Guy
  • 2,676
  • 4
  • 30
  • 54
  • 1
    Arnab: You got goal of the problem correct, but this solution will not work. Even for a limited database with in my example 100 users, i could have 2 times: John - Age 56. The update above would then generate an identical primary key. But the solution above gives a different primary. Thanks for thinking together! – Dimitri Dewaele Nov 07 '13 at 09:31
  • 1
    Okk.. i didnt know you would have duplicate rows.. I have modified my answer, so it should work now. However, ideally,I dont think you should have duplicate rows in your table – A Nice Guy Nov 08 '13 at 08:55
  • 1
    Arnab: Thanks. Your answer is now working. I have added a duplicate row in my question, based on your feedback. Nice work. – Dimitri Dewaele Nov 08 '13 at 09:48
  • why is the idea childish? – 3pitt Feb 05 '18 at 20:36