Tim Ward
2014-01-17 14:34:18 UTC
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.
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