At 11:11 AM 7/09/2006, you wrote:

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

Well, I can't see why it would be necessary. The current constraint
isn't a primary key, only a unique constraint, and you would need to keep that.

Just do alter table add aColumn IDField "ID" not null and commit
it; then (without doing anything else)

update activate_pmid
set IDField = (select Gen_ID ("ID", 1) from rdb$database)

./heLen