Re: 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).