How to Catch MySQLIntegrityConstraintViolationException: Duplicate Entry '22-85' for Key 'ID_CONTACT'

Table of Contents#

1. What is MySQLIntegrityConstraintViolationException?
2. Understanding the "Duplicate Entry '22-85' for Key 'ID_CONTACT'" Error
3. Common Causes of Duplicate Entry Errors
4. How to Reproduce the Error
5. How to Catch MySQLIntegrityConstraintViolationException in Code
6. Best Practices for Handling Duplicate Entries
7. Advanced: Preventing Duplicate Entries at the Database Level
8. Troubleshooting Tips
9. Conclusion
10. References

1. What is MySQLIntegrityConstraintViolationException?#

MySQLIntegrityConstraintViolationException is a specific type of SQLException thrown by the MySQL JDBC driver when a database operation violates an integrity constraint. Integrity constraints are rules enforced by the database to ensure data accuracy and consistency (e.g., unique keys, primary keys, foreign keys).

Key Context:#

• It is a subclass of SQLIntegrityConstraintViolationException (introduced in JDBC 4.0), which is part of the standard java.sql package.
• MySQL’s JDBC driver (e.g., mysql-connector-java) may wrap this exception in a vendor-specific class (e.g., com.mysql.cj.jdbc.exceptions.MysqlDataTruncation for truncation errors, but for duplicates, it directly uses MySQLIntegrityConstraintViolationException).
• This exception is triggered after the database rejects the operation due to a constraint violation, not before.

2. Understanding the "Duplicate Entry '22-85' for Key 'ID_CONTACT'" Error#

Let’s break down the error message:

• "Duplicate Entry '22-85'": The value 22-85 was attempted to be inserted or updated, but it already exists in the column associated with the unique key.
• "for Key 'ID_CONTACT'": ID_CONTACT is the name of the unique key constraint that was violated.

What is a Unique Key?#

A unique key is a database constraint that ensures all values in a column (or combination of columns) are distinct. Unlike primary keys, unique keys allow NULL values (but only one NULL, depending on the database). For example, if ID_CONTACT is a unique key on a contacts table, no two rows can have the same ID_CONTACT value.

Example Scenario:#

Suppose you have a contacts table defined as:

CREATE TABLE contacts (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    id_contact VARCHAR(50) NOT NULL,  
    name VARCHAR(100) NOT NULL,  
    UNIQUE KEY UK_ID_CONTACT (id_contact) -- Unique constraint on id_contact  
);  

If you insert a row with id_contact = '22-85':

INSERT INTO contacts (id_contact, name) VALUES ('22-85', 'John Doe');  

A subsequent attempt to insert another row with id_contact = '22-85' will trigger the error:

ERROR 1062 (23000): Duplicate entry '22-85' for key 'UK_ID_CONTACT'  

3. Common Causes of Duplicate Entry Errors#

Duplicate entry errors occur when your application tries to violate a unique key constraint. Here are the most frequent culprits:

1. Unvalidated Inserts#

Your application inserts data without first checking if the id_contact value already exists in the database.

2. Batch Operations#

Batch inserts/updates (e.g., using PreparedStatement.addBatch()) may include duplicate values for the unique key.

3. Race Conditions in Concurrent Systems#

In multi-threaded or distributed applications, two threads might check for the existence of '22-85' simultaneously, both determine it doesn’t exist, and then both attempt to insert it.

4. Incorrect Data Migration#

Data imports or migrations may accidentally include duplicate id_contact values.

5. Orphaned Unique Keys#

A unique key might be defined on a column you didn’t expect (e.g., a composite unique key involving id_contact and another column).

4. How to Reproduce the Error#

To understand the error better, let’s reproduce it step-by-step.

Step 1: Create the Table#

Use the contacts table definition from Section 2.

Step 2: Insert a Base Record#

INSERT INTO contacts (id_contact, name) VALUES ('22-85', 'John Doe');  

Step 3: Attempt a Duplicate Insert#

Now, try inserting another row with id_contact = '22-85':

INSERT INTO contacts (id_contact, name) VALUES ('22-85', 'Jane Doe');  

Result: MySQL throws the duplicate entry error.

Reproducing in Java (Without Error Handling)#

Here’s a Java snippet that will trigger the exception:

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.SQLException;  
 
public class DuplicateEntryExample {  
    private static final String URL = "jdbc:mysql://localhost:3306/your_db";  
    private static final String USER = "root";  
    private static final String PASSWORD = "your_password";  
 
    public static void main(String[] args) {  
        String sql = "INSERT INTO contacts (id_contact, name) VALUES (?, ?)";  
 
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);  
             PreparedStatement pstmt = conn.prepareStatement(sql)) {  
 
            // First insert (works)  
            pstmt.setString(1, "22-85");  
            pstmt.setString(2, "John Doe");  
            pstmt.executeUpdate();  
 
            // Second insert (duplicate)  
            pstmt.setString(1, "22-85");  
            pstmt.setString(2, "Jane Doe");  
            pstmt.executeUpdate(); // Throws exception here  
 
        } catch (SQLException e) {  
            e.printStackTrace(); // Prints MySQLIntegrityConstraintViolationException  
        }  
    }  
}  

Running this code will result in:

com.mysql.cj.jdbc.exceptions.MysqlIntegrityConstraintViolationException: Duplicate entry '22-85' for key 'contacts.UK_ID_CONTACT'  

5. How to Catch MySQLIntegrityConstraintViolationException in Code#

Catching this exception allows you to handle it gracefully (e.g., log the error, notify the user, or retry the operation). Here’s how to do it in Java.

Step 1: Identify the Exception Type#

MySQL’s JDBC driver throws com.mysql.cj.jdbc.exceptions.MysqlIntegrityConstraintViolationException, which is a subclass of SQLIntegrityConstraintViolationException. For portability, you can catch the standard SQLIntegrityConstraintViolationException, but catching the MySQL-specific variant gives more control.

Step 2: Catch and Validate the Error#

Use a try-catch block to intercept the exception. Check the error message to confirm it’s a duplicate entry for ID_CONTACT.

Example: Catching and Handling the Exception#

import com.mysql.cj.jdbc.exceptions.MysqlIntegrityConstraintViolationException;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.SQLException;  
 
public class CatchDuplicateEntryExample {  
    private static final String URL = "jdbc:mysql://localhost:3306/your_db";  
    private static final String USER = "root";  
    private static final String PASSWORD = "your_password";  
 
    public static void insertContact(String idContact, String name) {  
        String sql = "INSERT INTO contacts (id_contact, name) VALUES (?, ?)";  
 
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);  
             PreparedStatement pstmt = conn.prepareStatement(sql)) {  
 
            pstmt.setString(1, idContact);  
            pstmt.setString(2, name);  
            pstmt.executeUpdate();  
            System.out.println("Contact inserted successfully!");  
 
        } catch (MysqlIntegrityConstraintViolationException e) {  
            // Check if the error is a duplicate entry for ID_CONTACT  
            if (e.getMessage().contains("Duplicate entry") && e.getMessage().contains("UK_ID_CONTACT")) {  
                System.err.println("Error: Contact with ID " + idContact + " already exists.");  
                // Log the error (use a logging framework like SLF4J in production)  
                e.printStackTrace();  
            } else {  
                // Handle other integrity violations (e.g., foreign key constraints)  
                System.err.println("Database integrity error: " + e.getMessage());  
            }  
        } catch (SQLException e) {  
            // Handle other SQL errors (e.g., connection issues)  
            System.err.println("SQL error: " + e.getMessage());  
        }  
    }  
 
    public static void main(String[] args) {  
        insertContact("22-85", "John Doe"); // Success  
        insertContact("22-85", "Jane Doe"); // Fails with duplicate error  
    }  
}  

Key Takeaways:#

• Be Specific: Catch MysqlIntegrityConstraintViolationException (or SQLIntegrityConstraintViolationException) instead of the generic SQLException to avoid masking other errors.
• Check the Message: The error message contains details like the duplicate value and key name, which you can use to tailor your response.
• Log Details: Always log the full stack trace for debugging (use frameworks like Logback or Log4j instead of printStackTrace() in production).

6. Best Practices for Handling Duplicate Entries#

Catching the exception is just the first step. Proactively preventing duplicates and handling them gracefully is critical for robust applications.

1. Validate Data Before Insertion#

Check if the id_contact exists before attempting to insert:

private static boolean contactExists(String idContact, Connection conn) throws SQLException {  
    String sql = "SELECT 1 FROM contacts WHERE id_contact = ?";  
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {  
        pstmt.setString(1, idContact);  
        return pstmt.executeQuery().next(); // Returns true if exists  
    }  
}  
 
// In insertContact():  
if (contactExists(idContact, conn)) {  
    System.err.println("Contact already exists.");  
    return;  
}  
// Proceed with insert  

2. Handle Concurrency with Transactions#

Use database transactions to prevent race conditions. For example, wrap the "check-then-insert" logic in a transaction with a lock:

conn.setAutoCommit(false);  
try {  
    if (contactExists(idContact, conn)) { ... }  
    // Insert the contact  
    conn.commit();  
} catch (SQLException e) {  
    conn.rollback();  
    throw e;  
}  

3. Use Optimistic Locking#

For high-concurrency systems, use optimistic locking (e.g., with a version column) to detect conflicts without blocking threads.

4. Provide User-Friendly Messages#

Instead of showing the raw exception, tell users: "A contact with ID '22-85' already exists. Please use a unique ID."

5. Batch Operations: Validate First#

For batch inserts, validate all id_contact values upfront to avoid partial failures.

7. Advanced: Preventing Duplicate Entries at the Database Level#

If you can’t avoid duplicates in application logic, MySQL provides built-in mechanisms to handle them during insertion.

1. INSERT IGNORE#

Ignores the duplicate entry instead of throwing an error:

INSERT IGNORE INTO contacts (id_contact, name) VALUES ('22-85', 'Jane Doe');  

Pros: Simple.
Cons: Ignores all errors (not just duplicates), which can mask bugs.

2. ON DUPLICATE KEY UPDATE#

Updates existing rows instead of inserting duplicates:

INSERT INTO contacts (id_contact, name)  
VALUES ('22-85', 'Jane Doe')  
ON DUPLICATE KEY UPDATE name = VALUES(name); -- Updates name to 'Jane Doe'  

Use Case: Updating contact details if the id_contact already exists.

3. REPLACE#

Deletes the existing row and inserts a new one (use with caution—can cause data loss):

REPLACE INTO contacts (id_contact, name) VALUES ('22-85', 'Jane Doe');  

8. Troubleshooting Tips#

If you’re stuck, use these tips to diagnose the issue:

1. Verify the Unique Key#

Check if ID_CONTACT is indeed the unique key:

SHOW CREATE TABLE contacts;  

Look for UNIQUE KEY entries (e.g., UNIQUE KEY UK_ID_CONTACT (id_contact)).

2. Check for Composite Keys#

The error might involve a composite unique key (e.g., (id_contact, email)). Use SHOW CREATE TABLE to confirm.

3. Inspect the Duplicate Value#

Ensure '22-85' is the actual value causing the conflict. Use:

SELECT * FROM contacts WHERE id_contact = '22-85';  

4. Enable MySQL General Log#

Log all queries to see which operation is causing the duplicate:

SET GLOBAL general_log = 'ON';  
SET GLOBAL general_log_file = '/var/log/mysql/general.log';  

9. Conclusion#

MySQLIntegrityConstraintViolationException: Duplicate Entry '22-85' for Key 'ID_CONTACT' is a common but manageable error. By understanding its causes, catching it in code, and proactively validating data, you can ensure your application handles duplicates gracefully. Remember:

• Prevent first: Validate data and use transactions to avoid duplicates.
• Catch smartly: Use specific exception types and check error messages.
• Handle gracefully: Log details and communicate clearly with users.

10. References#

• MySQL Documentation: Unique Constraints
• Oracle JDBC Documentation: SQLIntegrityConstraintViolationException
• MySQL Connector/J Documentation
• Java Transactions Tutorial