Thread: Enforcing minimum on many-to-many relationship?
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
On Thu, Nov 29, 2012 at 1:03 PM, Mike Blackwell <mike.blackwell@rrd.com> wrote: > 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? From the documentation: "When a row-level AFTER trigger is fired, all data changes made by the outer command are already complete, and are visible to the invoked trigger function". So you need to make your trigger AFTER. > > The application is difficult to change, so I'd like to do this without > requiring it to call stored procedures if possible. > > Thoughts? > > > Mike > > -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray.ru@gmail.com