Can I use foreign key restrictions to return meaningful UI errors with PHP
- by Shane
I want to start by saying that I am a big fan of using foreign keys and have a tendency to use them even on small projects to keep my database from being filled with orphaned data. On larger projects I end up with gobs of keys which end up covering upwards of 8 - 10 layers of data.
I want to know if anyone could suggest a graceful way of handling 'expected errors' from the MySQL database in a way that I can construct meaningful messages for the end user. I will explain 'expected errors' with an example.
Lets say I have a set of tables used for basic discussions:
discussion
questions
responses
users
Hierarchically they would probably look something like this:
-users
--discussion
---questions
----responses
When I attempt to delete a user the FKs will check discussions and if any discussion exist the deletion is restricted, deleting discussion checks questions, deleting questions checks responses. An 'expected error' in this case would be attempting to delete a user--unless they are newly created I can anticipate that one or more foreign keys will fail causing an error.
What I WANT to do is to catch that error on deletion and be able to tell the end user something like 'We're sorry, but all discussions must be removed before you can delete this user...'.
Now I know I can keep and maintain matching arrays in PHP and map specific errors to messages but that is messy and prone to becoming stagnant, or I could manually run a set of selects prior to attempting the deletion, but then I am doing just as much work as without using FKs.
Any help here would be greatly appreciated, or if I am just looking at this completely wrong then please let me know.
On a side note I generally use CodeIgniter for my application development, so if that would open up an avenue through that framework please consider that in your answers.
Thanks in Advance
