By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,647 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,647 IT Pros & Developers. It's quick & easy.

how do you do a CASCADE delete in MySQL?

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Sun, 22 Aug 2004 08:11:31 -0400, "NotGiven" <no****@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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.