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: