Firebird: How to assign id to existing rows from newly added generator

Question

I have a table which already contains some rows and does not have a primary key.

I want to add a primary key now, but for that I need to set unique values for the primary key field of the existing rows. How can this be done, using the newly added generator?

Answer 1

Primary key does not generate any values by itself. It is constraint. In your example you need do next:

1. Add column for PK value, declare it NOT NULL
2. Fill in the column with unique values
3. Create PK constraint

Example:

CREATE TABLE test (a varchar(20));

COMMIT;

INSERT INTO test (a) VALUES ('A');
INSERT INTO test (a) VALUES ('B');
INSERT INTO test (a) VALUES ('C');

COMMIT;

ALTER TABLE test ADD pk INTEGER NOT NULL;

CREATE GENERATOR g_test;

COMMIT;

UPDATE test SET pk = GEN_ID(g_test, 1);

ALTER TABLE test ADD CONSTRAINT test_pk
  PRIMARY KEY (pk);