Deleting over multiple tables

[Gilead]

I'm looking to be able to delete data that spans over several tables, but I'm not sure how to do it. I could use a ton of DELETE statements, but is there a way to streamline the process? Thanks!

[topr8]

will you always be doing the same series of DELETEs? i'm assuming that the different tables are all connected by primary keys?

if so you could set up a TRIGGER on the database, set it to fire when you make a delete from 'table_A' ... the trigger should be programmed to delete from table_b, table_c or whatever you want.
that way you will only have to do the delete from table_a as the other deletes will happen automatically.
the downside of this is if you ever want to only delete from table_a then you won't be able to without the trigger firing.

alternatively create a stored function (stored procedure) on the database with all the statements in it (wrapped up in a transaction to ensure all deletes are done), and then just call the stored procedure from your php

alternatively you should be able to build a single MySQL statement to delete from multiple tables at once ... [dev.mysql.com...]

[Gilead]

I've tried the TRIGGER, but it's not working.
$sql = "CREATE TRIGGER `delete_vehicle` AFTER DELETE ON `vehicle`
FOR EACH ROW BEGIN
DELETE FROM 'oilchange' WHERE 'VehicleID'='".$id."';
DELETE FROM 'battery' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'brakes' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'custom' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'eos' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'muffler' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'tires' WHERE 'VehicleID' = '".$id."';
DELETE FROM 'wireplugs' WHERE 'VehicleID' = '".$id."';
END;
";
Where am I going wrong?

[Demaestro]

Assuming you want to delete a signle record accress many tables.

The best option is to add foreign key indexes and to define them to cascading delete.

That way you only have to delete the "parent" record and all related records will delete with it.

For example a user who can make a post.

User
----------
id
name
email


Post
----------
id
user_id
message

You would add a foreign key to Post table, and set to cascade on delete

ALTER TABLE Post
FOREIGN KEY (user_id) REFERENCES User (id) ON DELETE CASCADE

Then if you delete a row from the User table, any related Posts will be deleted.

Hope this helps

[Gilead]

Actually what I want to do is delete all entries with the id across all tables. Is a TRIGGER the right way to do that?