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