--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

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

Thanks again Helen.
Cheers
maurice