1

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?

4
  • 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?
    – Yoav
    Commented Jul 11, 2012 at 6:03
  • Defining the foreign keys with ON DELETE CASCADE may help you. Commented Jul 11, 2012 at 6:03
  • 1
    You have Alter the table structure have a look at dev.mysql.com/doc/refman/5.5/en/… Commented Jul 11, 2012 at 6:05
  • Please put it as an answer so can mark it and you get the rate :)
    – Yoav
    Commented Jul 11, 2012 at 6:14

2 Answers 2

1

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.

2
  • the table structure should by innodb. MyIsam does not work with this statment Commented Jul 12, 2012 at 6:27
  • Yes ofcourse!! As @Yoav is creating the FKeys in His tables so the engine will be InnDB. Commented Jul 12, 2012 at 7:00
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.