Handling Drupal SQL exceptions (db_insert, db_update, db_delete)

While developing a Drupal module, I just ran into a situation where it may be common for users to generate SQL exceptions. I have a 'unique' limit on several of my database tables, essentially saying that certain name fields must be unique for the current project. As you can imagine, it's extremely easy to enter a duplicate name, and while doing a SQL INSERT that can easily lead to a SQL exception.

As a result, I dug around and found a way to handle SQL exceptions in Drupal queries. My current problem is with a Drupal 7 db_insert query, and I handled it like this:

try {
  $id = db_insert('process_groups')
      ->fields(array(
         'project_id' => $process_groups->project_id,
         'name' => $process_groups->name,
  ))
  ->execute();
} catch (Exception $e) {
  # TODO can search $e for 'Integrity constraint violation' or 'Duplicate entry'
  # to verify the user tried to use the same name twice.
  drupal_set_message(t("Sorry, that didn't work. The name must be unique."), 'error');
}

The important things I'm showing here are:

• The db_insert query, which performs a SQL INSERT query.
• The try/catch block around the db_insert query. This catches any exceptions that are thrown.
• Handling the exception in the catch block.

I've actually done some things wrong in my Drupal 7 form, so I'm currently working on rewriting it, but I wanted to share some code to show how to handle a Drupal SQL exception. This is a correct way to handle SQL query exceptions, and should work not only with db_insert, but db_query, db_delete, and db_update as well.

As a final note, as you can see from my comments, I can search the MySQL exception that occurs for strings like 'Integrity constraint violation' or 'Duplicate entry' to verify that my unique constraint was violated, but since I'm rewriting this code to work differently, I didn't bother to go down that road.

In summary, if you're in the market for a Drupal SQL query exception example, I hope this has been helpful.