mysql - Database - handling of unique constraint violation

Question

I have a user creation screen that takes various user details along with first name and mobile number. I have a corresponding USER table in which the First Name and the Mobile number form a composite unique key. There are other integrity constraints defined on this table as well.

When user data is entered on the Create User screen that violates this constraint, user needs to be shown an 'user friendly' error message.

When such a violation occurs, the exception that I get from the MySQL database is:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1-1' for key `uk_FIRST_NAME__MOBILE_idx`

There are two options to show a meaningful message (for example: "ERROR: User name already exists for the given mobile number, please change either one of them").

Option 1: In the catch block of this exception, parse the MySQL exception's message and look for 'uk_FIRST_NAME__MOBILE_idx'. If present, show the user friendly message as mentioned above.

Option 2: Write a DAO level API that will take first name and mobile number as the only two parameters, fire a database query to see if there is an existing record matching this first name/mobile combination. If true, show the error message to the user; else, run an insert query to insert the user of record into the USER table.

I do NOT like the option 1, as it needs me to 'parse' the exception message, which is not a clean solution. I do NOT like the Option 2 as well, as it needs me to run 'two queries' on the database which is less efficient than the option 1, which is a single query solution.

Question: Are there any other options which are better than these two? If not, which one is the right approach among the above two?

Answer 1

I think "option 2" (manually checking constraint before attempting to insert) is horrible, not just because of the race hazard (which could be avoided with locking reads), but also (as you note) because of the additional load on the database: after all, to manually check the constraints completely negates the purpose and benefit of using constraints within the database.

I agree that parsing error message strings feels "dirty", but the strings are well defined. One could even refer to the underlying errmsg.txt or source header files.

Once one has extracted the key name from the error message, one can use the KEY_COLUMN_USAGE information schema to identify the offending columns:

public static final int ER_DUP_ENTRY = 1062;
public static final int ER_DUP_ENTRY_WITH_KEY_NAME = 1586;

public static final String REGEX_DUP_ENTRY_WITH_KEY_NAME =
  "Duplicate entry '(.*)' for key '(.*)'";

// ...


try {
// ...
} catch (MySQLIntegrityConstraintViolationException e) {
  switch (e.getErrorCode()) {
    case ER_DUP_ENTRY:
    case ER_DUP_ENTRY_WITH_KEY_NAME:
      Pattern p = Pattern.compile(REGEX_DUP_ENTRY_WITH_KEY_NAME);
      Matcher m = p.matcher(e.getMessage());

      SQLQuery query = session.createSQLQuery(
      " SELECT COLUMN_NAME" +
      " FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE" +
      " WHERE  CONSTRAINT_SCHEMA = :schema" +
      "    AND CONSTRAINT_NAME   = :key"
      );
      query.setString("schema", "my_schema");
      query.setString("key"   , m.group(2));

      showDuplicateError(query.list());

      break;
  }
}