Re: Multiple table relationship constraints

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Jack Christensen
> Sent: Thursday, May 05, 2011 3:20 PM
> To: pgsql
> Subject: [GENERAL] Multiple table relationship constraints
>
> 4. Validate application side -- this can work well, but it leaves the hole
of a
> bug in the application or a direct SQL statement going bad.
>
>
> Anyone have any advice on the best way to handle this?
>

Not totally following the usage though I have come across similar
requirements before.  A variant of #4 would be to remove
INSERT/UPDATE/DELETE permissions on the relevant tables and write SECURITY
DEFINER functions to perform those actions instead. You can additionally
leave the constraints loose and have the function query the tables
post-modification to make sure they are still valid (kind of like the
materialized view option but without a permanent table).

David J.