Foreign key references to non-primary key columns

On 5 Dec 2000, Camm Maguire wrote:

Greetings! I've noticed in the documentation that the sql standard
requires foreign keys to reference primary key/(or maybe just unique)
columns, but that postgresql does not enforce this. Is this a feature
that is intended to persist, or a temporary deviation from the sql
standard? The current postgresql behavior seems useful in cases where
one wants to update a foreign key to a value already in the original
table.

It's intended to be temporary and theoretically is in fact checked in 7.1
(although you could remove the index afterwards and it doesn't complain
-- necessary because you might need to drop/create the index for other
reasons).

The limitation is on the referenced columns, and the reason for it is that
if the referenced columns are not unique, parts of the RI spec stop making
sense as written. If you have match full and update cascade, and two pk
rows with key 1 and an fk row with key 1, what happens when you modify
the key value on just one of those pk rows? We could theoretically extend
the spec to make sense in these cases, but we have enough trouble with the
spec as is (match partial is amazingly awful).

Greetings, and thanks so much for your reply!

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

On 5 Dec 2000, Camm Maguire wrote:

Greetings! I've noticed in the documentation that the sql standard
requires foreign keys to reference primary key/(or maybe just unique)
columns, but that postgresql does not enforce this. Is this a feature
that is intended to persist, or a temporary deviation from the sql
standard? The current postgresql behavior seems useful in cases where
one wants to update a foreign key to a value already in the original
table.

It's intended to be temporary and theoretically is in fact checked in 7.1
(although you could remove the index afterwards and it doesn't complain
-- necessary because you might need to drop/create the index for other
reasons).

The limitation is on the referenced columns, and the reason for it is that
if the referenced columns are not unique, parts of the RI spec stop making
sense as written. If you have match full and update cascade, and two pk
rows with key 1 and an fk row with key 1, what happens when you modify
the key value on just one of those pk rows? We could theoretically extend
the spec to make sense in these cases, but we have enough trouble with the
spec as is (match partial is amazingly awful).

This is clearly a problem. I've played with this a bit, and the
current behavior is that deleting one of the two pk rows deletes the
fk row if on delete cascade is set. Haven't yet checked update, but I
bet it works the same way. And while a little messy, it still seems
better than having a unique constraint on a pk row in the following
circumstance, for example.

Say you input a bunch of data with one field denoting the 'identity'
of the entity referred to. But occasionally at some later date, this
identity field will change, while still referring to the same entity.
A cusip for a stock is a good example -- the cusip uniquely references
a given stock, but a given company can change its cusip at some
point. One would like to have a cusip,id table, with cusip as the pk,
but id as the fk in all the main data tables. On cusip change, one
merely updates the id for the new cusip to be the id of the old
cusip. On update cascade ensures that this propagates for any tables
that may be using id as a fk.

Doing it the other way looks like this: have a cusip,id table, with
id here now a fk pointing to another table ids with pk id. Have
ids.id the referenced column in all tables using a fk. But now one
cannot simply update idnew = idold if idold is already in the table,
so one writes an update trigger which basically updates all the fk
rows using idnew to idold, deletes idnew from ids, and returns null.

The only problem with this approach is that one must remember to
include all new tables with an fk id into this trigger when that table
is added. The trigger properly belongs to the table with the fk, but
should be fired on update to ids. Foreign keys seem exactly designed
to do this.

In any case, I take it from your recommendation that one should not
design a database around this current postgresql behavior for future
compatibility reasons. Any suggestions are most welcome.

Take care,

--
Camm Maguire camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens." -- Baha'u'llah