Alban Hertroys wrote:
Hi all,
We migrated a database from version 7.3 something to 7.4.7 a while ago,
and ever since that time we can't make new foreign keys to a particular
table.
You shouldn't have been able to before.
> The problem is that the primary key on that table is on two
columns that are unique together, but that only one of them should be
referenced from the other table.
Well there you go - a foreign-key must reference a set of rows with a
unique constraint (i.e. a candidate-key).
Tables are as follows:
CREATE TABLE localization (
localization_id text NOT NULL,
language_id integer NOT NULL REFERENCES
language(language_id) MATCH FULL,
content text NOT NULL
PRIMARY KEY (localization_id, language_id)
);
CREATE TABLE description (
description_id serial PRIMARY KEY,
content text NOT NULL REFERENCES
localization(localization_id)
);
I'm not sure how we got the "content" column from "description" to
reference "localization" back in version 7.3. Fact is, we can't seem to
do this anymore since version 7.4:
I don't have 7.3.x to hand any more, but if you could create such a
reference it was a bug. What you need to do is create a table to record
which (unique) localization_id codes you have, so:
CREATE TABLE loc_ids (
localization_id text NOT NULL,
PRIMARY KEY (localization_id)
);
CREATE TABLE localization (
localization_id text NOT NULL REFERENCES loc_ids,
language_id integer NOT NULL REFERENCES language,
content text NOT NULL,
PRIMARY KEY (localization_id, language_id)
);
CREATE TABLE description (
description_id SERIAL,
content text NOT NULL REFERENCES loc_ids,
PRIMARY KEY (description_id)
);
Of course, this seems to show that the "description" table isn't telling
you anything you couldn't work out by adding a serial column to loc_ids.
Perhaps you have more columns in it though.
You can setup triggers/views etc to automatically insert into loc_ids if
you would like.
Does that help?
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
