Can I use foreign key restrictions to return meaningful UI errors with PHP

Question

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

Answer 1

Sounds like you need to define your foreign keys with ON DELETE CASCADE. This will delete any referenced data in other tables.

Answer 2

You shouldn't be relying on the database to create errors for your application code. the FK's are there for when your app code messes up and tries to delete something it shouldn't.

If you really want to give the user a nice error message you will have to run the selects first, and build the appropriate error message.

edit

You can check for foreign keys in one select. If you are using an ORM like doctrine, you don't even have to specify the join, just tell it what fields to select, then check each table for nonzero rows.

Answer 3

Sadly, MySQL does not expose the ability to define a custom error like you would with SQL Server or Oracle.

• Bug/Feature Request #16999
• Worklog #2110 spec's the behavior for v5.5

Workaround

---

Check this blog post about using a UDF to be able to define custom errors.