pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Constraints...
3+ messages / 3 participants
[nested] [flat]

* Constraints...
@ 2001-05-12 18:57 Michael Richards <michael@fastmail.ca>
  2001-05-13 00:38 ` Re: Constraints... Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael Richards @ 2001-05-12 18:57 UTC (permalink / raw)
  To: pgsql-sql

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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; 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 <scrappy@hub.org>
To: <pgsql-sql@postgresql.org>
Subject: multi-table join, final table is outer join count ...
Message-ID: <Pine.BSF.4.33.0105121839260.629-100000@mobile.hub.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@hub.org           secondary: scrappy@{freebsd|postgresql}.org




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Constraints...
  2001-05-12 18:57 Constraints... Michael Richards <michael@fastmail.ca>
@ 2001-05-13 00:38 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-05-13 00:38 UTC (permalink / raw)
  To: Michael Richards <michael@fastmail.ca>; +Cc: pgsql-sql


Yes.  It depends on what exactly you want for the update/delete cases
on permissions.  I believe that in any cases you can use the
check function that's used by the fk implementation to do the
insert/update check on objects.  If you don't mind update/deletes
on permission failing if the row being modified has any references
(Even if there are others) you can use the fk functions for that
too - you'll have to make the triggers manually).
If you want update/deletes on permission to only fail (cascade,
whatever) when the *last* row matching a referencing row is deleted
(rather than any update/delete causing it) you'll probably need to
make a function like those in ri_triggers.c that makes sure there
isn't another row, which will involve locking the other matching
rows in permissions I think if you went that route.)


On Sat, 12 May 2001, Michael Richards wrote:

> 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');




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* constraints,
@ 2001-06-20 19:07 jeff@tsunamicreek.com
  0 siblings, 0 replies; 3+ messages in thread

From: jeff@tsunamicreek.com @ 2001-06-20 19:07 UTC (permalink / raw)
  To: pgsql-sql

Hello folks,

wondering how to display any constraints that a table may have,
as well i know alter table add constraint works, but what's the
syntax to remove one ?

i assume /alter table remove constraint, but i can't find any examples.

jeff





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-06-20 19:07 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-12 18:57 Constraints... Michael Richards <michael@fastmail.ca>
2001-05-13 00:38 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-20 19:07 constraints, jeff@tsunamicreek.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox