[GENERAL] foreign key restrictions

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