Re: Foreign Key Columns And Indices

On Mon, 5 Feb 2001, Philip Warner wrote:

> PGSQL implements PK/FK & Unique constraints by using indexes (and rules) at
> the moment. There is no guarantee that this will always be the case - in
> fact, one path to rationalizing the constraints system is to implement most
> features as SQL CHECK constraints:
>
> PK: Check( (Select Count(*) from Table Where PKCOLS=PKCOLS) = 1)
> FK: Check( (Select Count(*) from PK_Table Where PKCOLS=FKCOLS) = 1)

There are a couple of problems with this for the fk case. The biggest
is that check constraints with subselects won't currently do the
correct thing (even if it allowed you to specify them). We could
replace the current trigger on insert/update of fk table with a check
constraint assuming we made it possible to defer check constraints,
but all of the stuff on the pk table won't work that way due to
the referential actions and the fact that you need to check after delete
on pk rows (which AFAIK we don't currently do for check constraints).

The spec gives the check version of the foreign key constraint but it
was unfortunate that they also added functionality which then made that
insufficient. :(