How do automaticly delete rows from other tables

Question

I have a MySql DB.

I have a table - recipes. Also have 2 tables -ingredients and instructions. both have for each entry - a foreign key to the related recipe.

I want that when I delete an entry from the recipes table - to automaticly delete the related ingredients entries and instruction entries.

How?

From your comment it seems that I have to add the ON DELETE CASCADE to the table. Will that deleted the related entries? How can I alter the table to support it?
Defining the foreign keys with ON DELETE CASCADE may help you.
You have Alter the table structure have a look at dev.mysql.com/doc/refman/5.5/en/…
Please put it as an answer so can mark it and you get the rate :)

Abdul Manaf · Accepted Answer · 2012-07-11 10:18:01Z

up vote 0 down vote accepted

Whatever you are trying to do can easily be done by

USING ON DELETE CASCADE AND ON UPDATE CASCADE(IF Required)

While creating the foreign keys on your table.

For your reference have a look at FOREIGN KEY Constraints.

edited Jul 11 '12 at 10:18

answered Jul 11 '12 at 10:09

Abdul Manaf
1,556830

	Exactly what needed. Thanks – Yoav Jul 11 '12 at 10:14
	the table structure should by innodb. MyIsam does not work with this statment – raheel shan Jul 12 '12 at 6:27
	Yes ofcourse!! As @Yoav is creating the FKeys in His tables so the engine will be InnDB. – Abdul Manaf Jul 12 '12 at 7:00

raheel shan · Answer 2 · 2012-07-11 06:01:10Z

up vote 0 down vote

You can write a query like this

DELETE t1, t2 FROM t1 
INNER JOIN t2 on t1.id = t2.t1_id 
INNER JOIN t3 on t1.id = t3.t1_id
WHERE t1.id=t2.id AND t2.id=t3.id;

answered Jul 11 '12 at 6:01

raheel shan
1033

2 Answers