Re: Referencing "less-unique" foreign keys

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