I can't delete records from MySql

Question

I have two tables. table a references table b I believe.

When I try to delete the package alltogether like this:

$query="DELETE a, b FROM classified as a, $sql_table as b WHERE a.ad_id = '$id' 
AND a.classified_id = b.classified_id AND a.poster_password='$pass'";

b MUST be deleted first I guess. Even in PhpMyAdmin I cant delete a if b is still there, so I delete b first.

But what decides the order in which comes first?

The tables are alla InnoDB.

What should I do?

Thanks

Answer 1

Your Delete syntax is invalid. You need to do this in two statements (unless as nuqqsa mentioned, you have CASCADE DELETE enabled on the relationship between table a and table b):

Delete From b
Where Exists    (
                Select 1
                From a
                Where a.poster_password = '$pass'
                    And a.ad_id = '$id'
                    And a.classified_id = b.classified_id
                )

Delete From a
Where a.poster_password = '$pass'
    And a.ad_id = '$id'

What decides which comes first is the foreign keys relationships. Whichever table is the parent table must be deleted from last.

Answer 2

The MySQL manual says about multi-table DELETE and foreign keys:

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

So that when a record in your main table is deleted, so are its foreign references, e.g:

ALTER TABLE products
  ADD CONSTRAINT fk_supplier
      FOREIGN KEY (supplier_id, supplier_name)
      REFERENCES supplier(supplier_id, supplier_name)
      ON DELETE CASCADE;