How to fix Duplicate Key violates unique constraint
- Finn Gale
Scenario
A SQL Unique constraint exception can be thrown when Active objects attempts to store an item in a table using an existing unique value, such as an ID field. As multiple entries in the database cannot have the same ID a ‘Unique Constraint violation’ is thrown. To resolve the error the Sequence (SEQ) responsible for assigning unique values to new database entries needs to be altered.
As mentioned, this error is no specific to any one product, in this example I have used SU for Jira.
SQL Exception
SU failed: There was a SQL exception thrown by the Active Objects library:
Database: - name:PostgreSQL - version:10.17 (Ubuntu 10.17-1.pgdg20.04+1) - minor version:17 - major version:10 Driver: -
name:PostgreSQL JDBC Driver - version:42.2.23 org.postgresql.util.PSQLException:
ERROR: duplicate key value violates unique constraint "AO_935429_SUAUDITLOG_pkey" Detail: Key ("ID")=(2) already exists.
Solution
To fix this issue the SEQ for the Table in the database needs to be updated, in this case the table: "AO_935429_SUAUDITLOG". These changes must be made to the database and can be carried out interactively, or through commands. In this example I have used SQL commands for a PostgreSQL database.
Find the last value added to the table, using the key shown in the error message
SELECT MAX("ID"") FROM "AO_935429_SUAUDITLOG";
Find the last value used by the SEQ.
SELECT Last_value FROM "AO_935429_SUAUDITLOG_ID_seq";
From this outcome we see that the last value used by the SEQ was bellow the highest value for the ID. To resolve this, the SEQ’s current value should be set to 5.
Set the SEQ’s current value to the tables last entry + 1
ALTER SEQUENCE "AO_935429_SUAUDITLOG_ID_seq" RESTART WITH 5;
After re-starting Jira the new value for the SEQ will be applied fixing the error.