Enforcing minimum on many-to-many relationship?

From: Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Enforcing minimum on many-to-many relationship?
Date: 2012-11-29 21:03:47
Message-ID: CANPAkgunte+i1YMHJDEeZZWchWbz-exCVp-Uo3iRCqwn=ATDmg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a pair of tables, and a third describing a many-to-many relationship
between them. Along the lines of:

CREATE TABLE a (a_id integer NOT NULL PRIMARY KEY);
CREATE TABLE b (b_id integer NOT NULL PRIMARY KEY);
CREATE TABLE x (a_id integer NOT NULL REFERENCES a(a_id) ON DELETE CASCADE,
b_id integer NOT NULL REFERENCES b(b_id) ON DELETE CASCADE);

I want to be able to enforce the condition that there will always be at
least one row in table 'x' for each row in 'a'. I.e, a row in 'a' must
always be related to at least one 'r'.

My first thought was a trigger at delete time. That works, BUT, the
trigger also apparently fires on a FK cascade, preventing deleting a row in
'a' since the cascade will attempt to delete all the rows in 'x'.

Is it possible to disable or otherwise bypass the trigger on cascade
without affecting other transactions?

The application is difficult to change, so I'd like to do this without
requiring it to call stored procedures if possible.

Thoughts?

Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Giannakopoulos 2012-11-29 21:06:43 About aggregates...
Previous Message Tom Lane 2012-11-29 20:16:59 Re: pg_listening_channels()