7.1 REFERENCES contstraints

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

-Michael
_________________________________________________________________
http://fastmail.ca/ - Fast Free Web Email for Canadians
>From pgsql-sql-owner(at)postgresql(dot)org Sun May 6 21:34:09 2001
Received: from megazone23.bigpanda.com ([216.136.151.41])
by postgresql.org (8.11.3/8.11.1) with ESMTP id f471Y5A08805
for <pgsql-sql(at)postgresql(dot)org>; Sun, 6 May 2001 21:34:05 -0400 (EDT)
(envelope-from sszabo(at)megazone23(dot)bigpanda(dot)com)
Received: from localhost (sszabo(at)localhost)
by megazone23.bigpanda.com (8.11.2/8.11.2) with ESMTP id f471XvJ74266;
Sun, 6 May 2001 18:33:57 -0700 (PDT)
Date: Sun, 6 May 2001 18:33:57 -0700 (PDT)
From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Michael Richards <michael(at)fastmail(dot)ca>
cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: 7.1 REFERENCES contstraints
In-Reply-To: <3AF5C845(dot)00008D(dot)78696(at)frodo(dot)searchcanada(dot)ca>
Message-ID: <Pine(dot)BSF(dot)4(dot)21(dot)0105061830030(dot)74241-100000(at)megazone23(dot)bigpanda(dot)com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200105/42
X-Sequence-Number: 2843

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."