Re: 7.1 REFERENCES contstraints

On Sun, 6 May 2001, Michael Richards wrote:

> I see that the REFERENCES constraint is a little more restrictive in 
> 7.1. I need to have a table with a constraint so one of it's columns 
> must exist in a second table. This is not a key, since there may be N 
> columns in the second table that match. Any ideas on how I should 
> create this?
> 
> CREATE table foo(
> id serial,
> permissions int4,
> FOREIGN KEY (permissions) REFERENCES bar(permid));
> 
> used to work in 7.0 but now it complains with:
> psql:ddl.sql:103: ERROR:  UNIQUE constraint matching given keys for 
> referenced table "permissions" not found

You can't do this using references.  SQL requires that the second table
must have a unique constraint on those columns.  The semantics of
match partial would be close (but we don't have that yet), but the
match unspecified and match full semantics don't really work right
if it isn't unique (for example, any deletes from the second table
where referenced would fail even if there were additional rows
that could satisfy the constraint, also, cascaded deletes would
for example happen on the first deletion of a matching row).  While
you could make the constraint in 7.0, it really didn't work "right."