Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4CIweA96580 for ; Sat, 12 May 2001 14:58:40 -0400 (EDT) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id 1E8622110; Sat, 12 May 2001 14:57:56 -0400 (EDT) MIME-Version: 1.0 Message-Id: <3AFD87B4.000021.34508@frodo.searchcanada.ca> To: pgsql-sql@postgresql.org Subject: Constraints... From: "Michael Richards" X-Fastmail-IP: 24.43.130.237 Date: Sat, 12 May 2001 14:57:56 -0400 (EDT) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200105/106 X-Sequence-Number: 2922 Does anyone know how I can make a constraint on a key to enforce a 1 to n relationship where n>0? I've invented an example to show the sort of constraint I need: CREATE TABLE permissions ( id int4, userid int4, perm int4, primary key (id,userid) ); CREATE TABLE objects ( id int4, perm int4 NOT NULL, data text ); INSERT INTO permissions (id,userid,perm) VALUES (1,1001,7); INSERT INTO permissions (id,userid,perm) VALUES (1,1002,6); INSERT INTO permissions (id,userid,perm) VALUES (1,1003,4); INSERT INTO permissions (id,userid,perm) VALUES (2,1001,0); So I want to allow something like: INSERT INTO objects (id,perm,data) VALUES (1,1,'everyone can read'); INSERT INTO objects (id,perm,data) VALUES (2,1,'everyone can read'); INSERT INTO objects (id,perm,data) VALUES (3,2,'nobody can read'); But disallow an insert like: INSERT INTO objects (id,perm,data) VALUES (9999,1,'bad perm example'); Is this possible? -Michael _________________________________________________________________ http://fastmail.ca/ - Fast Free Web Email for Canadians From pgsql-sql-owner@postgresql.org Sat May 12 17:54:23 2001 Received: from mobile.hub.org (SHW12-37.accesscable.net [24.71.155.37]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4CLicA90615 for ; Sat, 12 May 2001 17:44:38 -0400 (EDT) (envelope-from scrappy@hub.org) Received: from localhost (scrappy@localhost) by mobile.hub.org (8.11.3/8.11.1) with ESMTP id f4CLiaF93456 for ; Sat, 12 May 2001 18:44:36 -0300 (ADT) (envelope-from scrappy@hub.org) X-Authentication-Warning: mobile.hub.org: scrappy owned process doing -bs Date: Sat, 12 May 2001 18:44:35 -0300 (ADT) From: The Hermit Hacker To: Subject: multi-table join, final table is outer join count ... Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200105/107 X-Sequence-Number: 2923 Okay, not sure best way to try and describe this ... have multiple tables, of a form like: table a gid int data text table b gid int data text table c gid int data text table d gid int data text I want to return: a.gid,a.data,b.data,c.data,count(d.data) where a.gid = b.gid = c.gid = d.gid *but* I want count(d.data) to return zero *if* there are no records in table d ... essentially, gid has to exist in tables a,b,c but not d ... So, ignoring table d, i'd have: SELECT a.gid,a.data,b.data,c.data FROM tablea a, tableb b, tablec c WHERE a.gid = b.gid AND b.gid = c.gid; How do I add 'tabled d' to the mix? Thanks ... Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org