Thread: Referencing "less-unique" foreign keys

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

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:

psql> ALTER TABLE description ADD CONSTRAINT fk_description_content
FOREIGN KEY (content) REFERENCES localization(localization_id);
ERROR:  there is no unique constraint matching given keys for referenced
table "localization"

Any way around this?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

//Showing your Vision to the World//

On Tue, Aug 09, 2005 at 02:31:16PM +0200, 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. 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.

Foreign keys have to reference a column that has only unique values.
This is what the SQL standard requires of FOREIGN KEYS. If your
localization_id in the localization table is unique, just add a UNIQUE
index, problem solved.

If localization_id is not unique but you really want foreign keys,
you'll have to create a table containing only localization_ids and have
both tables foreign key to that...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

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

Martijn van Oosterhout wrote:
> On Tue, Aug 09, 2005 at 02:31:16PM +0200, 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. 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.
>
>
> Foreign keys have to reference a column that has only unique values.
> This is what the SQL standard requires of FOREIGN KEYS. If your
> localization_id in the localization table is unique, just add a UNIQUE
> index, problem solved.
>
> If localization_id is not unique but you really want foreign keys,
> you'll have to create a table containing only localization_ids and have
> both tables foreign key to that...

I was afraid that would be the only answer... It's the way I would have
solved it too - would I have the time.

SELECTs and UPDATEs aren't influenced by the change, but INSERTs and
DELETEs (w/ cascade) are. Am I right that this could be fixed
transparently (to our queries) by creating a few RULEs on localization
on INSERT and DELETE? That'd certainly save some time...

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

//Showing your Vision to the World//

Tom Lane wrote:
> Having just tried it, I can say that the last version that would take
> that without complaint is 7.0.  7.1 and later give variants of
>
> ERROR:  UNIQUE constraint matching given keys for referenced table "localization" not found
>
> So I'm not sure what Alban actually did.

Neither am I. This database was created before I was responsible for
this project, a few years ago - by someone who has left already (which
is a good thing, in his case).

There were 2 or 3 triggers on each refering/referencing table, of which
I assume they were generated by postgresql. I do recall that creating
foreign keys stopped working suddenly (maybe due to an update by our
sysadmin), after which we created the triggers by hand... The "we" here
usually not including me.

I would like to show some of those triggers, but due to the clutter
caused by all the foreign keys we created that way up til last year or
so, that's quite an ordeal... There are tables where the tabel
definition scrolls out of view rather rapidly...

I never liked the way this was solved by my predecessor, this seems a
good opportunity to fix it.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

//Showing your Vision to the World//

Richard Huxton <dev@archonet.com> writes:
> Alban Hertroys wrote:
>> 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.

Having just tried it, I can say that the last version that would take
that without complaint is 7.0.  7.1 and later give variants of

ERROR:  UNIQUE constraint matching given keys for referenced table "localization" not found

So I'm not sure what Alban actually did.

            regards, tom lane