Re: foreign key restrictions - Mailing list pgsql-general

<rafal@zorro.isa-geek.com> wrote in message
news:64cc57edd02dabd82e3f95268aee1a67.squirrel@localhost...
> Hi All,
>
> on numerous times I had fell onto postgress complaining, that I try to
> create foreign key, pointing to a set not embraced within a unique key
> constraint.
>
> Here is the case:
>
> CREATE TABLE one (id int not null unique, info text);
> CREATE TABLE two (id int not null unique, ofone int references one(id),
> info text);
>
> now when I try to:
>
> CREATE TABLE three(one int not null, two int, info text, foreign key (one,
> two) references two (one, id));
>
> I get the following error:
> ERROR:  there is no unique constraint matching given keys for referenced
> table "two"
>
> But.
>
> Since table TWO has a unique constraint on column ID, the (ID, OFONE) pair
> will also be unique, obviously.
>
> Naturaly I can, and I do, add the requested constraint to the table TWO,
> but to me it looks spurious - not providing any additional constraint
> (which is already quearanteed by unique(ID), just a 'syntax glue'.
>
> I must have missed something here. Can someone help me understand this?
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Postgresql is being faithful to the SQL standard. ISO/IEC 9075-2:2003 11.8
<referential constraint definition> says:

"If the <referenced table and columns> specifies a <reference column list>,
then there shall be a one-to-one correspondence between the set of <column
name>s contained in that <reference column list> and the set of <column
name>s contained in the <unique column list> of a unique constraint of the
referenced table such that corresponding <column name>s are equivalent."

I don't think there is any sound justification(*) for this limitation but it
is shared by other SQL DBMSs too. Most are incredibly lame when it comes to
support for multi-table constraints. The general type of constraint you are
referring to is often called an "inclusion dependency". Probably the reason
why it isn't well supported is that the optimisation of such constraints
within the limitations of SQL is potentially quite a hard problem.

(*) Note that the term "FOREIGN KEY" is misleading anyway. The constraint
that SQL calls a FOREIGN KEY is not the same as what the relational model
calls a "foreign key". In the RM, convention has it that only referential
constraints that reference candidate keys are called foreign keys whereas
SQL allows its FOREIGN KEY to reference any columns declared as unique (ie
may be a super key rather than a candidate key).

--
David Portas