Constraints...

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(at)postgresql(dot)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 <pgsql-sql(at)postgresql(dot)org>; Sat, 12 May 2001 17:44:38 -0400 (EDT)
(envelope-from scrappy(at)hub(dot)org)
Received: from localhost (scrappy(at)localhost)
by mobile.hub.org (8.11.3/8.11.1) with ESMTP id f4CLiaF93456
for <pgsql-sql(at)postgresql(dot)org>; Sat, 12 May 2001 18:44:36 -0300 (ADT)
(envelope-from scrappy(at)hub(dot)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 <scrappy(at)hub(dot)org>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: multi-table join, final table is outer join count ...
Message-ID: <Pine(dot)BSF(dot)4(dot)33(dot)0105121839260(dot)629-100000(at)mobile(dot)hub(dot)org>
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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org