On Mar 31, 2010, at 8:24 AM, Jared Williams wrote:
I don't think it is possible to implement a robust cascading delete in an application without transaction support. (eg MyISAM) You need a transaction to ensure either all the delete statements run, or none of them.
Yes, this is correct -- but you have to roll back the transaction manually, and it's up to you to detect an unsatisfied reference if you don't use foreign key constraints.
If you use database-enforced constraints, you don't even have to use explicit transactions. The database constraints ensure that all cascading operations succeed, or else they all fail.
For those people who insist that they can make their database stay consistent (that is, all references are satisfied at all times) by enforcing it in their PHP space, I'd suggest you run some quality control SQL scripts to check for orphaned child rows.
Regards, Bill Karwin
