Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: DELETE statement help
04-19-2013, 08:05 AM #1
- Join Date
- Feb 2012
- Thanked 0 Times in 0 Posts
DELETE statement help
I have a guest id that exists in 3 different tables. I want to delete a guest in one step. How do I use the DELETE statement to delete all data associated with that guest id in all 3 tables with out having to do it 3 separate times ?
Here's what I have:
DELETE FROM guests WHERE guest_id = $guest_id DELETE FROM guest_dates WHERE guest_id = $guest_id DELETE FROM links WHERE guest_id = $guest_id
DELETE FROM guests, guest_dates, links WHERE guest_id = $guest_id
What other way can I accomplish this ?
04-19-2013, 08:43 PM #2
The only way you could do that in one step would be:
(a) Be sure you are using INNODB tables, not MyISAM.
(b) Be sure you have specified correct PRIMARY KEY on the guests table.
(c) Be sure you have specified correct FOREIGN KEY contrasints on the other two tables.
(d) Be sure that, in the FOREIGN KEY contraints, you specify ON DELETE CASCADE.
CREATE TABLE guests ( guest_id INT PRIMARY KEY, ... ) ENGINE INNODB; CREATE TABLE guest_dates ( guest_id INT, CONSTRAINT FOREIGN KEY guest_id REFERENCES guests(guest_id) ON DELETE CASCADE, ... ) ENGINE INNODB; CREATE TABLE links ( guest_id INT, CONSTRAINT FOREIGN KEY guest_id REFERENCES guests(guest_id) ON DELETE CASCADE, ... ) ENGINE INNODB;
DELETE FROM guests WHERE guest_id = 999;
Otherwise, you must delete from each table individually.An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.