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