>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.

What's the reason you think you need another ID column, Tim? If it is adding branches or something, an alternative might be to have different ranges, either letting one branch have, say, 1-999999, another 1000000-1999999 etc. Another option is to have different offsets for each branch and use GEN_ID(<gen>, 100) rather than GEN_ID(<gen>, 1) so that one branch gets 101, 201, 301... another branch 102, 202, 302 etc. There may be other reasons for you trying to partition your PK, if you told us why, then maybe someone may come up with a simpler way to solve your problem than add another part to the PK.

HTH,
Set