Home › Java exceptions › SQLIntegrityConstraintViolationException

What it looks like

java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'asha@example.com' for key 'users.uk_email'

Extends SQLNonTransientException in java.sql.

What it means

The database rejected a write because it would break a rule defined in the schema. The vendor message names the constraint, which is the most useful part: a duplicate key (unique or primary key), a foreign key that points at a missing row or is still referenced, or a NOT NULL column that received null. Drivers differ in the exact type thrown (PostgreSQL throws a generic PSQLException with SQLState 23505 for unique violations), but SQLState class 23 always means integrity constraint violation.

Common causes

• Inserting a row whose unique or primary key value already exists, often because of a race between two requests.
• Inserting a child row for a parent that does not exist, or deleting a parent that still has children.
• Saving an entity with a null in a NOT NULL column, for example a missing default.
• Batch imports that reuse ids, or a sequence that is out of step with existing data.

How to fix it

1. Read the constraint name in the message and map it back to the schema.
2. For duplicates, check first and also handle the exception: the check alone has a race, the constraint is the real guarantee.
3. Use upserts (INSERT ... ON CONFLICT, MERGE) where idempotency is intended.
4. Delete children before parents or define ON DELETE CASCADE where the model calls for it.

Broken and fixed

if (!repo.existsByEmail(email)) {
    repo.save(new User(email)); // two requests can both pass the check
}

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

🔎 Unravel the failing code 🔎 Unravel the fix

In Spring and enterprise code

Spring translates this into DataIntegrityViolationException (or DuplicateKeyException for unique violations), so you will usually catch the Spring type, not the java.sql one.

Debug it faster

Paste the whole stack trace into the stack trace beautifier to hide framework frames, jump to the root cause and find the first line of your own code. It runs in your browser, so the trace never leaves your machine.

Related exceptions