Skip to content
javahacks.dev
Hibernate

Fix SQLIntegrityConstraintViolationException on Insert

Read integrity violation errors for MySQL and PostgreSQL, tell unique, foreign key and not-null failures apart and handle them without losing the real bug.

6 min read
Summarize in AI
Flat illustration of a log with a failing line and a wrench, with the Hibernate, Spring, Gradle and Apache Maven logos.

An insert that worked in development fails in production:

Text
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '[email protected]' for key 'users.uk_users_email'

On PostgreSQL the same family appears as:

Text
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "users_email_key"
  Detail: Key (email)=([email protected]) already exists.

The database enforced a rule you gave it. That is the database doing its job, and the question is why the application produced a row that breaks the rule. Three constraint types account for nearly all of it.

Classify by SQLState

SQLStateMeaningTypical message
23505Unique violationDuplicate entry / duplicate key value
23503Foreign key violationCannot add or update a child row / violates foreign key constraint
23502Not null violationColumn 'name' cannot be null / null value in column
23514Check violationviolates check constraint

In JDBC, SQLException.getSQLState() gives the code, which is more stable than the message text across databases and driver versions.

Unique violation

Two causes: a real duplicate input, and a race. For the first, report it to the user. For the second:

java
// Check-then-insert is racy
if (!users.existsByEmail(email)) {
    users.save(new User(email));        // another request can insert between the two lines
}

Two requests can both pass the check. The unique constraint is the only guard that works, so catch its violation:

java
try {
    users.save(new User(email));
} catch (DataIntegrityViolationException e) {
    throw new EmailAlreadyUsedException(email);
}

In SQL, an upsert avoids the exception when "already there" is fine:

SQL
insert into user_prefs (user_id, theme) values (?, ?)
on conflict (user_id) do update set theme = excluded.theme;

MySQL spells it insert ... on duplicate key update. Idempotent endpoints, covered in making endpoints idempotent, rely on the same constraint.

Foreign key violation

Text
Cannot add or update a child row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`))

The customer_id you inserted does not exist in customer. Look for an id passed from the client without validation, a parent that was deleted, or a parent created in a transaction that has not committed (in another connection, it is invisible). In Hibernate, a related message is [TransientPropertyValueException](/library/transientpropertyvalueexception-fix-save-spring-data) when the parent was never saved.

The delete direction fails too: deleting a parent that still has children violates the key. Delete children first, or declare on delete cascade where deletion should propagate, deliberately.

Not-null violation

Text
ERROR: null value in column "name" of relation "customer" violates not-null constraint

A field was never set. Common sources: a JSON field missing from the request and not validated, an entity mapping that uses a different column name, a default expected from the database that the mapping overrides with null. Add validation (@NotBlank) at the API boundary so the client gets a 400, with the constraint as the backstop.

Return a good error from Spring

java
@ExceptionHandler(DataIntegrityViolationException.class)
ProblemDetail conflict(DataIntegrityViolationException e) {
    ProblemDetail p = ProblemDetail.forStatus(HttpStatus.CONFLICT);
    p.setTitle("Data conflict");
    p.setDetail("The request conflicts with existing data.");
    return p;
}

Do not return the exception message to clients, because it shows table, column and constraint names. Log it, and return a general text. A more precise handler can inspect the constraint name (e.getMostSpecificCause()) and map known unique constraints to field errors.

The SQLState codes and vendor codes in one place

Database drivers report a standard five-character SQLState, and the first two characters say the class. Class 23 is an integrity constraint violation, and the full code narrows it down. Vendors add their own error number as well.

ViolationSQLStateMySQL errorPostgreSQL message starts with
Unique constraint23505 (PostgreSQL), 23000 (MySQL)1062duplicate key value violates unique constraint
Foreign key23503 (PostgreSQL), 23000 (MySQL)1452 (insert), 1451 (delete)violates foreign key constraint
Not null23502 (PostgreSQL), 23000 (MySQL)1048null value in column
Check constraint235143819violates check constraint

MySQL uses 23000 for all of the family, so read the error number there. Branching on e.getSQLState() and e.getErrorCode() is more reliable than parsing the message text, which changes with the server's language setting.

The check-then-insert race

A common fix for duplicates is to ask first:

java
if (!repository.existsByEmail(email)) {
    repository.save(new User(email));
}

Two requests arriving together both see false and both insert, and the second fails on the constraint. The code is not wrong in a single-threaded test, and it fails under load. The constraint in the database is the only thing that settles the race, so keep it and handle the failure. If the intent is "insert or ignore", say so in SQL:

SQL
-- PostgreSQL
insert into users (email, name) values (?, ?) on conflict (email) do nothing;

-- MySQL
insert ignore into users (email, name) values (?, ?);

on conflict do update and on duplicate key update turn it into an upsert, for the case where the existing row should change.

A failed statement poisons the transaction on PostgreSQL

After a constraint error inside a transaction, PostgreSQL marks the transaction as aborted, and every later statement fails with current transaction is aborted, commands ignored until end of transaction block. A Java catch block that swallows the exception and goes on to run another query then fails with a confusing second error. Either let the exception roll back the whole transaction, or use a savepoint with REQUIRES_NEW for the part that is allowed to fail. MySQL continues the transaction after a failed statement, so code that works there can break when the database changes.

Translate it once at the boundary

Spring turns these exceptions into DataIntegrityViolationException, and a handler can map it to a useful response with a stable status:

java
@RestControllerAdvice
class ConstraintAdvice {

    @ExceptionHandler(DataIntegrityViolationException.class)
    ProblemDetail onConflict(DataIntegrityViolationException e) {
        ProblemDetail p = ProblemDetail.forStatus(HttpStatus.CONFLICT);
        p.setDetail("A record with the same unique value already exists");
        return p;
    }
}

A 409 tells the client the request was valid and clashed with existing data, while a 500 says the server failed. Do not put the constraint name or the SQL in the response, which tells an attacker about the schema. Log the cause, and return a message that is safe to show.

Prove the constraint with a test

A test that inserts the same value twice and expects the exception pins the behaviour, and it catches a migration that dropped the index:

java
@Test
void duplicateEmailIsRejected() {
    repository.saveAndFlush(new User("[email protected]"));
    assertThrows(DataIntegrityViolationException.class,
            () -> repository.saveAndFlush(new User("[email protected]")));
}

Use saveAndFlush, since a plain save may not hit the database until the transaction ends, and the test would pass without the check running.

Constraints are documentation

A named constraint tells the next developer what the data model promises. Name them yourself in the migration script, such as uk_users_email and fk_order_customer, instead of accepting generated names. The names then appear in error messages, in the translation from a violation to a user message, and in code review. A handler can map uk_users_email to "that email is already registered" without parsing text that changes between database versions.

Keep the mapping in one place, a small table from constraint name to message, and add a test that every constraint named in the table exists in the schema. A renamed constraint then fails a test, not a user's registration.

Where this does not apply

Check constraints from a domain rule and exclusion constraints in PostgreSQL appear with other SQLStates; look at the code. Data loads that violate constraints in bulk should be fixed in the source, not by disabling constraints. Hibernate surfaces the same errors, as covered in the Hibernate guide, and the pool behind them in connection timeouts. The PostgreSQL error codes appendix lists every class, and the [JDBC SQLException documentation](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/SQLException.html) describes getSQLState.

Share

Keep reading

Get new posts by email

We send one email when a new post is published. No spam.

Unsubscribe in one click. No spam, ever.