Re: Multiple table relationship constraints

On Thu, May 5, 2011 at 4:14 PM, Jack Christensen <jackc(at)hylesanderson(dot)edu>wrote:

> It's not denormalized. It is an attribute that both tables have that have
> to match for it to be a valid link.
>
> Here's a contrived example:
>
> CREATE TABLE dorms(
> dorm_id serial PRIMARY KEY,
> gender varchar NOT NULL,
> ...
> );
>
> CREATE TABLE people(
> person_id serial PRIMARY KEY,
> gender varchar NOT NULL,
> ...
> );
>
> CREATE TABLE room_assignments(
> person_id integer NOT NULL REFERENCES people,
> dorm_id integer NOT NULL REFERENCES dorms,
> ...
> );
>
> Men should only be assignable to men's dorms and women should only be
> assignable to women's dorms. On occasion a person's or dorm's gender needs
> to be updated. I want to make sure that doesn't cause a room assignment to
> become invalid. In this example, adding gender to room_assignments and using
> composite foreign keys is fairly straight forward -- but in my actual domain
> I have 5+ tables and 2+ attributes involved in the relationship.
>

Hm. I think the way I would handle this is to put the business logic for
inserting/updating into the room_assignments table into one or more
functions and have a special user that owns the tables and owns the
functions and declare the functions to be SECURITY DEFINER. Revoke
INSERT/UPDATE/DELETE access to the tables from all other users. Then you
grant your regular users EXECUTE access to the functions. The functions run
as the user that created them, so they will have direct INSERT/UPDATE/DELETE
access to the tables while your regular users won't.

--
Rick Genter
rick(dot)genter(at)gmail(dot)com