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

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/

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

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

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