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.