Re: Inserting a new PK into an existing table

"Jens Lenge" <spampot_at_gmx.net> writes:

> 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?
>

First add the column

alter table yo add (
id number
);

Then use update statements to populate the column. This is left as an exercise to the reader.

Then make it a primary key

alter table yo add constraint pk_yo
primary key (id)
using index tablespace IDX;

--
Dave Newman