Delete all entries in a MxN relation with non-null FK constraints

Question

I have two entities A and B that are related in a MxN relation through an intermediary table, thus making a total of three tables. My relation table R has non-nullable FK constraints on the other two tables.

I want to delete all entries from tables A and B and R where A obeys some constraint (I can supply the ids in A table for instance).

Is it possible to delete entries from the three tables without resorting to a stored procedure and without violating the non-null constraint?

I want something like:

delete from A a, B b, R r where a.id=r.fk_a and B.id=r.fk_B a

Answer 1

It depends. If the fk between r and b was specified with ON DELETE CASCADE, you could do:

START TRANSACTION;

DELETE FROM b
WHERE  id IN (
    SELECT r.b_id
    FROM       r
    INNER JOIN a
    ON         r.a_id = a.id
    WHERE      <some condition on a>
);

DELETE FROM a
WHERE      <some condition on a>;

COMMIT WORK;

If there is no cascading delete, then you can do it with a temporary table:

CREATE TEMPORARY TABLE to_be_deleted_from_b 
LIKE b;

START TRANSACTION;

INSERT INTO to_be_deleted_from_b
SELECT * 
FROM b
INNER JOIN r
ON         b.id = r.b_id
INNER JOIN a
ON         r.a_id = a.id
WHERE      <some condition on a>;

DELETE FROM r
WHERE  a_id IN (
    SELECT a.id
    FROM   a
    WHERE  <some condition on a>
);

DELETE FROM a
WHERE  <some condition on a>;

DELETE FROM b
WHERE b.id IN (
    SELECT id
    FROM   to_be_deleted_from_b
);

COMMIT WORK;

DROP TABLE to_be_deleted_from_b