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.
thanks in advance,