I am writing a MySQL driven web application. I have a master table and some tables referencing the master table. I am using InnoDB as the storage engine and have set up a few foreign keys with 'ON DELETE CASCADE'.
Having set up the database like this; from PHP, all I need to do is delete the record from the master table and MySQL will delete the related rows automatically. But when I look at the code, it will be very difficult to trace how the data from another table is getting deleted without a query associated to it.
So coming to my question, is it a good idea to use foreign keys or would it be cleaner to instead write multiple queries to delete the rows? Or should I set up the foreign keys and also run the queries to delete the related rows.
The way you are doing it is best since it requires fewer queries and hence allows the database more control to work out the most efficient way of doing the deletes. Anyone who proplery understands how databases work will not have any problem figuring out your code.
I need to respectfully disagree with Felgall. cascading FKs are one of the worst things you can do to a database.
It makes for inconsistent code and difficult to trace bugs. No foreign key should ever be able to perform a cascading delete. In fact the very purpose of foreign keys is to prevent this.
Instead, you should be explicit with ALL of your DML, enlisting transactions if necessary.
Reply With Quote
Bookmarks