Hi

I want to run a cascading delete that deletes say a restaurant ( id=2) from my restaurant table but cascade delete all the dishes that are related to that restaurant.

Could some one please showe me how i might do this.

The key for restaurant table id id
The key for dish table is restaurant_id

Any help would really be appreciated. Bit stuck on this.

Cheers

Are you using foreign keys? If you're using innodb you can create a foreign key and set it to cascade, for example you could add the following to your dish table definition:

FOREIGN KEY (restaraunt_id) REFERENCES restaurant(id) ON DELETE CASCADE

Now if a restaurant is deleted, any corresponding dishes will be deleted as well.

No, No foriegn Keys
Just two tables linked by restaurant.id and dish.restaurant_id

Your

"FOREIGN KEY (restaraunt_id) REFERENCES restaurant(id) ON DELETE CASCADE"

comment would actually be defined in the DB on the create YES?

This hasnt been done by person who created DB so again no... Just two tables.

Looking to create best SQL i can from what have

To Add

I was looking at how i could do with a trigger?

I think you can set a foreign key in the dish table that refers to the restaurant table, with a cascading delete attached to it. However, I also think you have to use the InnoDB storage engine (dont think MyISAM supports foreign keys).

If your concern in the first place is that you don't wanna run two seperate queries in case the first one succeed and the second one fails, another alternative is to use transactions (also supported by InnoDB but not MyISAM). Transactions ensure that either all queries are successfully run, or none is. If one fails, everything is "rolled back" to their state before the transaction.

EDIT: Oh, I was too slow. I opened this tab 20 minutes ago and didn't post until now, seems I wasn't quick enough :P

OK I understand
thanks for you input.
In truth, i think i am going to have to go the the person and see why he has built the Db the way he has and see if he can update things.

Cheers again.

Steve

By the way, what is your original problem? Can't you just run two queries?
delete from dish where restaurant_id=2
delete from restaurant where id=2

Yes thats how i am doing at moment.
2 completely separate queries.

It was just suggested to me i should be able to cascade the delete. ( by employer )

On looking into this, i realized that it build with myisam engine not InnoDB so this just got me thinking if i could do in a more efficient way.

I found some info on triggers but didnt quite understand it. It was on the back en of that i questioned how i could delete more efficiently.

CREATE TRIGGER restaurant_cascade_delete

I suppose my question still stands how i can use a trigger to delete

Originally Posted by lizciz

dont think MyISAM supports foreign keys

This is true. You must be using the innodb storage engine to use foreign keys.

If you're already using innodb or there isn't a strong reason why you can't switch (relying on myisam full text search would be one) my advice would be to redesign the table to use foreign keys.

I'm moving this thread to the DB forum since the problem isn't strictly related to PHP.

This might be of some help if you want to create a trigger:
http://stackoverflow.com/questions/5...storage-engine

Ah yes thats quite helpful.
Thanks