How to delete a row from every connected table

Question

CREATE TABLE X(
id INTEGER primary key
);

CREATE TABLE XconY(
id INTEGER not null references x(id),
id2 varchar(20) not null references Y(id2),
);

CREATE TABLE Y(
col BOOLEAN,
id2 varchar(20) PRIMARY KEY
);

And my goal is to delete a value from table X and it should delete the associated value from XconY and Y.

INSERT INTO X VALUES (1);
INSERT INTO XconY VALUES (1,"hello");
INSERT INTO Y VALUES (1,"hello");

DELETE FROM X WHERE id = 1;

After the following set of these instructions all 3 tables should be empty. Other than making individual queries is there a better way to do this?

Does this answer your question? [MySQL foreign key constraints, cascade delete](https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete) — Kaan, Aug 02 '22 at 19:06
Yeah I saw that earlier, but I can't seem to add that into my schema, as in I can't get Y to have a foreign key on Y so it deletes itself. — AlphaDJog, Aug 02 '22 at 19:14

score 0 · Accepted Answer · answered Aug 02 '22 at 19:56

MySQL supports multi-table DELETE syntax (this is not standard SQL and may not be supported if you use other brands of database).

mysql> select * from X join XconY using (id) join Y using (id2);
+-------+----+------+
| id2   | id | col  |
+-------+----+------+
| hello |  1 |    1 |
+-------+----+------+
1 row in set (0.00 sec)

mysql> delete X, XconY, Y from X join XconY using (id) join Y using (id2);
Query OK, 3 rows affected (0.01 sec)

mysql> select * from X join XconY using (id) join Y using (id2);
Empty set (0.00 sec)

That's perfect. Thank you. the other solution only erased foreign keys and it wasn't optimal. This is exactly what I was looking for. — AlphaDJog, Aug 03 '22 at 11:47

How to delete a row from every connected table

1 Answers1