Re: Foreign key problem

On 25 Jun 2001, Itai Zukerman wrote:

> According to the documentation for CREATE TABLE:
>
> In addition, the referenced columns are supposed to be the columns
> of a UNIQUE constraint in the referenced table, however Postgres
> does not enforce this.
>
> Well, it looks like PostgreSQL *does* enforce it. Try it with:

Hmm, that paragraph should have been ripped out at the same time I added
the check. Will see if I need to patch the docs (or if it was already
done by someone).

> CREATE UNIQUE INDEX IX_IdLabNr_KulturDetail
> ON ResKulturDetail(IdLabNr, IdIndex) ;
>
> I'm not sure why this restriction is necessary...

Mostly because the semantics of the constraint as defined by the spec
don't make sense in certain cases against non-unique pk table rows. For
example, a delete cascade would delete the *first* time a matched row was
deleted even if there were other rows that could be matched except in
MATCH PARTIAL (which we don't support). Once we support MATCH PARTIAL,
we might make the case to allow MATCH PARTIAL references to non-unique
columns as an extension, but we'd need to think through the other effects
of that.