Re: Constraints...

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