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.

An insert that worked in development fails in production:
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '[email protected]' for key 'users.uk_users_email'On PostgreSQL the same family appears as:
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
| SQLState | Meaning | Typical message |
|---|---|---|
23505 | Unique violation | Duplicate entry / duplicate key value |
23503 | Foreign key violation | Cannot add or update a child row / violates foreign key constraint |
23502 | Not null violation | Column 'name' cannot be null / null value in column |
23514 | Check violation | violates 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:
// 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:
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:
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
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
ERROR: null value in column "name" of relation "customer" violates not-null constraintA 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
@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.
| Violation | SQLState | MySQL error | PostgreSQL message starts with |
|---|---|---|---|
| Unique constraint | 23505 (PostgreSQL), 23000 (MySQL) | 1062 | duplicate key value violates unique constraint |
| Foreign key | 23503 (PostgreSQL), 23000 (MySQL) | 1452 (insert), 1451 (delete) | violates foreign key constraint |
| Not null | 23502 (PostgreSQL), 23000 (MySQL) | 1048 | null value in column |
| Check constraint | 23514 | 3819 | violates 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:
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:
-- 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:
@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:
@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.


