Re: Inserting a new PK into an existing table

Jens Lenge wrote:
> Hello world,
>
> I want to add a new numeric column "id" into an existing table that is
> intended to be the new primary key. As the table already contains data,
> I need to fill the "id" column with distinct values before I can make
> it the new primary key.
>
> I have looked for SQL commands to add the new column and fill it with a
> series of values like 1, 2, 3, ..., but have not found something like
> that. As I am quite new to SQL, I have also read a number of tutorials,
> but found no hint on the topic.
>
> Now I am curious how this is "normally" done.
> Can it be done with "plain" SQL or do I need extensions like PL/SQL?
>
> Jens

Normally we use a business column or set of column values in the table to be the PK and do not use an artificial key since if a unique business value exists there is no need for or real use of an artificial key.

You can populate a numeric column with unique values by performing an update statement that references the rownum for each row in the table.

UT1 > select * from marktest;

FLD1 FLD2 FLD3

---------- ---------- ---------
one                 1 08-MAY-06
two                 2 08-MAY-06
three               3 08-MAY-06
two                22 26-MAY-06
three              33 26-MAY-06
five                5
six                   16-JUN-06
                   99 01-JAN-50

8 rows selected.

UT1 > update marktest set fld2 = rownum;

8 rows updated.

UT1 > select * from marktest;

FLD1 FLD2 FLD3

---------- ---------- ---------
one                 1 08-MAY-06
two                 2 08-MAY-06
three               3 08-MAY-06
two                 4 26-MAY-06
three               5 26-MAY-06
five                6
six                 7 16-JUN-06
                    8 01-JAN-50

8 rows selected.

HTH -- Mark D Powell -- Received on Thu Aug 31 2006 - 11:03:24 CDT