Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f46LtpA57065 for ; Sun, 6 May 2001 17:55:51 -0400 (EDT) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id AFA132135; Sun, 6 May 2001 17:55:17 -0400 (EDT) MIME-Version: 1.0 Message-Id: <3AF5C845.00008D.78696@frodo.searchcanada.ca> To: pgsql-sql@postgresql.org Subject: 7.1 REFERENCES contstraints From: "Michael Richards" X-Fastmail-IP: 24.43.130.237 Date: Sun, 6 May 2001 17:55:17 -0400 (EDT) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200105/41 X-Sequence-Number: 2842 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@postgresql.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 ; Sun, 6 May 2001 21:34:05 -0400 (EDT) (envelope-from sszabo@megazone23.bigpanda.com) Received: from localhost (sszabo@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 To: Michael Richards cc: pgsql-sql@postgresql.org Subject: Re: 7.1 REFERENCES contstraints In-Reply-To: <3AF5C845.00008D.78696@frodo.searchcanada.ca> Message-ID: 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."