MySQLism and PostgreSQLism: avoid deleting data in several tables in one query

SQL-99 does not allow to delete data in several tables in one query. The reasons for this are quite obscure. Maybe that it was considered more safely, for atomicity reasons, to use transactions and several queries. But there is no clear explanation.

MySQL allows to delete or uptable records on multiple tables.

SQL92 and PostgreSQL do not allow (per standard) to delete data in multiple tables.

DELETE h, tn, td
FROM history AS h
INNER JOIN term_node AS tn ON (h.nid = tn.nid)
INNER JOIN term_data AS td ON (td.tid = tn.tid)
WHERE h.uid = 2067 AND td.vid = 2

In MySQL, this query deletes all records in history, term_node and term_date at once.

PostgreSQL and the SQL Standard have other ways to delete from multiple tables:

• On DELETE CASCADE foreign keys can be used.
• Triggers can be used.

However, MySQL does not support either of these mechanisms reliably.

There is no easy fix to this problem; MySQL is unlikely to improve support for Foreign Keys, and PostgreSQL is unlikely to implement a feature which breaks the SQL standard.

To write portable code, we recommend not relying on multi-table deletes of any kind. Instead, delete from each table one at a time, or use switched code which executes differently per database backend.