Handling foreign key exceptions in PHP

Question

What is the best way in PHP to handle foreign key exceptions on a mysql database? Is there a mysql class that can be used to simplify any code?

Ideally, what I want to do, as an example, is to try to delete a record where it is the foreign key parent to any number of child tables. The foreign key throws the exception, so then I would like to be able to look at each foreign key table and test it, giving meaningful feedback on the tables and number of records causing the exception. This would then be returned as the error so the end user can reference and delete the offending records.

Answer 1

I think the best bet would be for you to do a transaction. That way, the insert will always be valid, or not done at all. That can return an error message that you can work with as well. This will prevent you from having to manually check every table - the db does it for you.

Answer 2

The way I handle this is to set up my database wrapper class to always throw an exception when you encounter a database error. So, for instance, I might have a class called MySQL with the following functions:

public function query($query_string)
{
    $this->queryId = mysql_query($query_string,$this->connectionId);
    if (! $this->queryId) {
        $this->_throwException($query_string);
    }
    return $this->queryId;
}

private function _throwException($query = null)
{
    $msg = mysql_error().".  Query was:\n\n".$query.
                "\n\nError number: ".mysql_errno();
    throw new Exception($msg,mysql_errno());
}

Any time a query fails, a regular PHP exception is thrown. Note that I would throw these from within other places too, like a connect() function or a selectDb() function, depending on whether the operation succeeded or not.

With that set up, you're good to go. Any place you expect that you might need to be handling a database error, do something like the following:

//assume $db has been set up to be an instance of the MySQL class

try {
    $db->query("DELETE FROM parent WHERE id=123");
} catch (Exception $e) {
    //uh-oh, maybe a foreign key restraint failed?
    if ($e->getCode() == 'mysql foreign key error code') {
        //yep, it failed.  Do some stuff.
    }
}

Edit

In response to the poster's comment below, you have some limited information available to you to help diagnose a foreign key issue. The error text created by a failed foreign key restraint and returned by mysql_error() looks something like this:

Cannot delete or update a parent row:
a foreign key constraint fails
(`dbname`.`childtable`, CONSTRAINT `FK_name_1` FOREIGN KEY
(`fieldName`) REFERENCES `parenttable` (`fieldName`));

If your foreign keys are complex enough that you can't be sure what might cause a foreign key error for a given query, then you could probably parse this error text to help figure it out. The command SHOW ENGINE INNODB STATUS returns a more detailed result for the latest foreign key error as well.

Otherwise, you're probably going to have to do some digging yourself. The following query will give you a list of foreign keys on a given table, which you can examine for information:

select * from information_schema.table_constraints
WHERE table_schema=schema() AND table_name='table_name';

Unfortunately, I don't think there's a magic bullet to your solution other than examining the errors and constraints very carefully.