Unique Constraint Violation Error

We will now refer to one of the most common database errors, the unique constraint violation.

In CUBRID, the error looks like this:

ERROR: DBMS, -670, Operation would have caused one or more unique constraint violations.

Just like in other database management systems, this error occurs during an INSERT or UPDATE statement and it is due to having a unique constraint on one of the fields inserted or updated.

Important!  The primary keys in CUBRID must be unique.

The procedure to use in order to find the cause of the problem:

• Step #1: Find out what fields have this constraint. Let's assume the table is named "foo".

  DESC foo;
  SHOW indexes in foo;

  The first command displays the table columns. Check for the ones with "PRI" type (primary key). The second command displays all the indexes. Look for the ones with Non_unique field (the second one) set to 0. More information on how to understand the fields can be found here: SHOW INDEX

• Step #2: Now that we know the fields, check the values for each of the fields using SELECT statements to see which one exists. If one of them exists, then you have found the reason of your problem. Either change the duplicate value or remove the unique constraint.
• Step #3 (optional): If step 2 didn't solve your problem, then you have to be on the lookout for AUTO_INCREMENT fields. AUTO_INCREMENT fields are only updated if they are not specified in INSERT statements or if they are given the null value when they are specified.

  Let's say your "foo" table has a "foo_id" AUTO_INCREMENT PRIMARY KEY. If you have imported the values from another database and you have manually set the value for foo_id, then the next AUTO_INCREMENT value has not been adjusted.

  Therefore, since AUTO_INCREMENT value was not updated in the above scenario, when you run INSERT commands without specifying foo_id (and relying on AUTO_INCREMENT to do it for you), the AUTO_INCREMENT will go to the next value and it may match one of the values you have manually inserted for this field.

  The solution for this scenario is to get the maximum value for foo_id + 1 using the first command below and then adjusting the table AUTO_INCREMENT value (For this example, we assumed the first command returns the value "10").

  SELECT MAX(foo_id)+1 from foo;
  ALTER TABLE foo AUTO_INCREMENT = 10;