mysqldatabaseintegrityreferential

MySQL delete table with regard to referential integrity rules

I want to delete tableA while removing all rows in tableB that have matching tableA.A_ID = tableB.A_ID

Currently I have a foreign key set to CASCADE on delete and update set on TableB for tableB.A_ID.

Solution

  • Turn constraints temporarily off by

    SET SQL_SAFE_UPDATES = 0;

DELETE FROM tableB b WHERE EXISTS ( SELECT * FROM tableA a WHERE a.A_ID = b.A_ID )

DELETE FROM tableA;

SET SQL_SAFE_UPDATES = 1;