ANSI SQL - 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

You could do that with three deletes and using a temporary table.

• First, add all the to be deleted records to a temporary table.
• Secondly delete from your intermediary table all the relations that fit your constraint.
• Third, delete from A all records that don't exist in the intermediary table.
• And finally, delete from B all records that don't exist in the intermediary table.

Example

BEGIN TRAN

INSERT INTO #R
SELECT R.*
FROM R r
     INNER JOIN A a ON a.ID = r.fk_a
WHERE a.Column = 'AConstraint'

DELETE FROM R
FROM R r
     INNER JOIN A a ON a.ID = r.fk_a
WHERE a.Column = 'AConstraint'

DELETE FROM A
FROM A a
     INNER JOIN #R r ON r.fk_a = a.ID

DELETE FROM B
FROM B b
     INNER JOIN #R r ON r.fk_b = b.ID
WHERE r.ID IS NULL

DROP TABLE #R

COMMIT TRAN

Answer 2

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