Deleting items from several MySQL tables

Anon

Is there a feature for this:

I have a MySQL database "pets" with three tables: "dogs", "cats" and "owner".

When I delete item (for example ID #5) from table "owner", I would like also delete all "cats" and "dogs" items with this owner (ID #5).

Now I need three separate queries, but is there a way to handle all this in one single MySQL query?

Anon

Well because you're using MySQL, which has pitiful referential integrity support, you can't use standard SQL to delete the related records.

However, I believe that in recent versions of MySQL you can actually delete from more than one table in a go:

delete from adsf,asdf,adsf where blah=blah;

Sort of thing. I'm not sure tho, I may be misremembering.

However, I suggest you switch to something like Postgres where you can create a foreign key between the tables where upon deleting the parent row, all child rows are automatically deleted.

Chris

Anon

I found out that the
"delete from adsf,asdf,adsf where blah=blah" is supported in MySQL 4.0 and up. The version 4.0 is an alpha release and not very widely used.

So I have to do three different queries.