CODEWITHSUNDEEP
sqlfirebird
zz1433
zz1433

Reputation: 3586

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

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?

Upvotes: 2

Views: 1860

Answers (1)

Andrej Kirejeŭ
Andrej Kirejeŭ

Reputation: 5481

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

Upvotes: 5

Related Questions