Atlassian uses cookies to improve your browsing experience, perform analytics and research, and conduct advertising. Accept all cookies to indicate that you agree to our use of cookies on your device. Atlassian cookies and tracking notice, (opens new window)
/
How to fix Duplicate Key violates unique constraint
Updated Aug 26

    How to fix Duplicate Key violates unique constraint

    • 1 Scenario
      • 1.1 SQL Exception
    • 2 Solution
      • 2.1 Find the last value added to the table, using the key shown in the error message
      • 2.2 Find the last value used by the SEQ.
      • 2.3 Set the SEQ’s current value to the tables last entry + 1

    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.

     

    The Plugin People
    Teams
    , (opens new window)

    Knowledge Base
    • How-to articles
      How-to articles
       This trigger is hidden
    Results will update as you type.
    • Enterprise Mail Handler - JEMH - (Server/DataCenter)
    • Enterprise Mail Handler - JEMHC - (Cloud)
    • EMQ
    • Custom Space User Management (CSUM)
    • Custom Space User Management (Cloud)
    • Project User Management for Jira
    • Future Oauth support in Jira for IMAP/SMTP
    • Switch User (SU)
    • How-to articles
      • How to fix Duplicate Key violates unique constraint
      • How to use Regular Expression (Regex)
    • Pages fail to load for Confluence 7.13.1, 7.14.0
    • Critical Vulnerability Exploits (CVE's)
    • GroupManager addUserToGroup method restricted
    • Jira's Inbound Mail Processing fails due to Basic Authentication Deprecation
    • XML Export Error
    • Gmail is adding the Outbound Mail within the Inbox
    • Velocity Allowlist Issues
      Calendars
    You‘re viewing this with anonymous access, so some content might be blocked.
    {"serverDuration": 9, "requestCorrelationId": "f04f207a217c47d39f38038b86173b8b"}