|Subject||Re: adding auto-increment field to existing table|
--- In firstname.lastname@example.org, Helen Borrie <helebor@...> wrote:
> At 11:11 AM 7/09/2006, you wrote:
> >I am trying to add an auto-increment field (trigger + generator) to an
> >existing table using ALTER TABLE statement. I wonder if this is
> >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,
> >keep that.
> >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
>Thanks again Helen.
> 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)