Deleting rows from multiple tables in MySQL

Question

Here is what i'm trying to do:

Delete a project from the projects table and all the images associated with that project in the images table.

Lets say $del_id = 10

DELETE FROM projects, images WHERE projects.p_id = '$del_id' AND images.p_id = '$del_id'

What is wrong with this query?

Answer 1

You should use two separate queries to do that :

delete from images where p_id = 123;

delete from projects where p_id = 123;

i.e. :

• First, delete the images, that depend on the project (foreign key ? )
• And, when nothing depends on the project anymore, delete the project itself.

And, as a security precaution, you should wrap all this in a transaction, to get a all or nothing behavior -- well, if you are using a storage engine that suppors transactions, like InnoDb.

See 12.3.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax, about that, in the MySQL Manual.