how do you do a CASCADE delete in MySQL?

I'd like to delete a record and all its children records at one time. How
do I do that?

Can you, in one SQL statement,

delete from table 1 where id = 3
delete from table 2 where id = 12
delete from table 3 where id = 12
......

?

Thanks in advance

On Sun, 22 Aug 2004 08:11:31 -0400, "NotGiven" <noname@nonegiven.net> wrote:

>I'd like to delete a record and all its children records at one time. How
>do I do that?
>
>Can you, in one SQL statement,
>
>delete from table 1 where id = 3
>delete from table 2 where id = 12
>delete from table 3 where id = 12
>.....

Yes, with foreign key constraints having an ON DELETE CASCADE clause.

mysql> create table parent (p_id int not null primary key) TYPE=INNODB;
Query OK, 0 rows affected (0.07 sec)

mysql> create table child (
-> c_id int not null primary key,
-> p_id int not null,
-> index(p_id),
-> foreign key (p_id) references parent (p_id)
-> on delete cascade
-> )
-> TYPE=INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from parent;
+------+
| p_id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.02 sec)

mysql> select * from child;
+------+------+
| c_id | p_id |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
+------+------+
3 rows in set (0.00 sec)

mysql> delete from parent where p_id = 1;
Query OK, 1 row affected (0.02 sec)

mysql> select * from parent;
+------+
| p_id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

mysql> select * from child;
+------+------+
| c_id | p_id |
+------+------+
| 3 | 2 |
+------+------+
1 row in set (0.00 sec)

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool