I'm not at all sure I'm going to like the answer to this one.

I've got a bunch of tables each with a numeric synthetic single column primary key, as in

CREATE TABLE A
(
    A_ID BIGINT NOT NULL,
    ... // other columns
    CONSTRAINT PK_A PRIMARY KEY( A_ID )
);

(and so for TABLE B, C etc, to M say).

I now need to partition these tables by some other factor Z_ID, which itself is a foreign key to somewhere else, so that they become

CREATE TABLE A
(
    A_ID BIGINT NOT NULL,
    Z_ID BIGINT NOT NULL,
    ... // other columns
    CONSTRAINT PK_A PRIMARY KEY( A_ID, Z_ID ),
    CONSTRAINT FK_A_Z FOREIGN KEY( Z_ID ) REFERENCES Z( Z_ID )
);

so that instead of containing no more than one row for A_ID = 42, it can now contain one row for A_ID = 42 for Z_ID = 17, and another row for A_ID = 42 but with a different Z_ID = 87.

There's an additional twist that some of these tables refer to each other, eg table C might have an A_ID column and a FOREIGN KEY( A_ID ) REFERENCES A( A_ID ), and this'll need to be changed to FOREIGN KEY( A_ID, Z_ID ) REFERENCES A( A_ID, Z_ID ), so the new column Z_ID in table C is both a column of the primary key and a column in a foreign key.

The question is: what has to go into the upgrade script?

(It probably doesn't help much, but I can guarantee that none of the tables affected will contain any data at the time we want to run the upgrade scripts, and the application will not be running.)

All I've found so far is suggestions that you have to drop the old primary key constraint and create the new one, which also means dropping and re-creating everything that references the old primary key, which at first sight sounds like a complete nightmare.

Is that really the only way?

If so, are there any useful recipes somewhere for finding all the dependent objects that need deleting and re-creating? - I'm aware that the system tables are documented in appendix V of the Firebird book but I'm afraid it's not immediately obvious to me how to use this information to identify the particular dependencies I'm after.

-- 
Tim Ward

-- 
Tim Ward