Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Jul 11 '12 at 6:03
    
Defining the foreign keys with ON DELETE CASCADE may help you. –  Abdul Manaf Jul 11 '12 at 6:03
1  
You have Alter the table structure have a look at dev.mysql.com/doc/refman/5.5/en/… –  Abdul Manaf Jul 11 '12 at 6:05
    
Thanks - exaclty what needed... –  Yoav Jul 11 '12 at 6:14
    
Please put it as an answer so can mark it and you get the rate :) –  Yoav Jul 11 '12 at 6:14
add comment

2 Answers 2

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.

share|improve this answer
    
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
add comment

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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