Recursive deletion in mysql

[Archbob]

I'm wondering how to do a 3 level deletion in two queries. Like I have categories, entries and comments for those entries. When I select to delete a category I want all entries under that categories and all comments under the entries to be deleted. Now I know how to do this for a while loop. But is there a way for 1 query to delete all the entries under a categories and all the comments under those entries?

[tbach2]

AFAIK, mysql doesn't have much to offer on this. It has to do with data integrity, which mysql apparently does not have.

I'm looking into using postgres for this reason-- you can tell the database which tables have records that are dependant on data from other tables; i.e. if you delete a row from one table, it can cascade delete on another.

But for mysql, it's up to you to keep track of your keys. If your data is recursive, you'll probably have to delete it recursively unless you have some id that distinguishes the groups. 🙁 (hope that makes sense...)

[eoghain]

Not if you are using the standard MySQL table type MyISAM. MyISAM doesn't have the ability for you to add table referances (foreign keys) and without foreign keys you can't add the ability to cascade deletes across tables.

[Archbob]

Actually I just checked the manual and mysql 4.x lets you do this, but I'm running 3.23 .