Hi,

I am trying to add an auto-increment field (trigger + generator) to an
existing table using ALTER TABLE statement. I wonder if this is possible?

For example,

CREATE DOMAIN "ID" AS NUMERIC(18, 0);
CREATE GENERATOR "ID";

create table activate_pmid(
pmid numeric(10,0) not null,
subject char(50) not null,
object char(50) not null,
version char(10) default '0',
constraint activate_unique unique (pmid, subject, object, version));

There is about 500000 records in activate_pmid. I want to add an ID
field of type ID into activate_pmid, so the end result is an
incremental field (ID) in activate_pmid where the original 500k
records have a running number.

Is there a better way than creating a temporary table (t) and do a
"insert into t select * from activate_pmid"
"drop table activate_pmid"
re-create activate_pmid with the auto-incrementing field + trigger
insert everything from t to the new activate_pmid?

Thank you.
Cheers
Maurice