--- In firebird-support@yahoogroups.com, "Maurice Ling" <beldin79@...>
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?

If you really want to make it totally like an autoinc field, you can
use a before insert or update trigger, then run a dummy update. I
assume by the way that you are testing if there is a value already.

eg

(IF NEW.ID IS NULL) THEN NEW.ID = GEN_ID(ID, 1);

You are going to run into troubles if you name everything ID.

In any case, you can do this:

ALTER TABLE activate_pmid ADD ID IDDOMAIN NOT NULL;
COMMIT;
UPDATE activate_pmid SET
ID = GEN_ID(activate_pmidID, 1)
WHERE ID IS NULL;
COMMIT;

-- NOW DROP YOUR PK CONSTRAINT
-- NOW ADD NEW PK CONNSTRAINT ON NEW ID FIELD
-- Now add unique constraint to old PK field if applicable.

Adam