How to fix Transaction integrity constraint violation in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 28, 2025· 195 views

PostgreSQL aborts transactions when data violates constraints like unique keys, foreign keys, or CHECK rules. Learn to identify which constraint failed, fix data conflicts, and use deferrable constraints to prevent transaction rollbacks.

Quick fix

Fixes Transaction integrity constraint violation

ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=(user@example.com) already exists.
  1. Review the error message for constraint name and details
  2. Check for duplicate values in UNIQUE or PRIMARY KEY columns
  3. Verify foreign key references exist before inserting child records
  4. Use deferrable constraints to allow temporary violations
postgresql — error
ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=(user@example.com) already exists.
Transaction integrity constraint violation

What this error means

A transaction integrity constraint violation occurs when an INSERT, UPDATE, or DELETE operation attempts to violate a database constraint. PostgreSQL has strict transactional semantics: when any constraint is violated, the entire transaction is automatically aborted and rolled back. This is different from some databases that allow partial failure recovery. The error includes details about which constraint was violated (e.g., unique constraint, foreign key, or check constraint) and the specific values that caused the conflict. This behavior ensures data consistency but requires applications to handle constraint violations gracefully. The most common causes are duplicate primary/unique keys, foreign key references to non-existent records, or values outside CHECK constraint ranges.

Typical symptoms

  • Transaction fails immediately when executing INSERT or UPDATE statement
  • Subsequent commands in the transaction are ignored with "ERROR: current transaction is aborted" message
  • Entire transaction rolls back, losing all previous changes in that transaction
  • Application receives constraint violation error with constraint name and conflicting values
  • Concurrent transactions deadlock when both try to insert same unique value

Common causes

  1. Inserting duplicate values into a PRIMARY KEY or UNIQUE constraint column
  2. Inserting child records with foreign key references that do not exist in parent table
  3. Updating a record with values that violate an existing constraint
  4. Concurrent transactions attempting to insert the same unique value (one waits, then fails on second attempt)
  5. VALUES in INSERT/UPDATE statement violate a CHECK constraint rule
  6. Deleting parent records while child records still reference them with ON DELETE NO ACTION

How to fix "Transaction integrity constraint violation"

1Review the error message for constraint name and details

PostgreSQL includes helpful information in the error message:

bash
ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=(user@example.com) already exists.

or

bash
ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(999) is not present in table "customers".

Identify whether it's a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint violation. The constraint name helps you locate the definition in your schema.

2Check for duplicate values in UNIQUE or PRIMARY KEY columns

If the error is about UNIQUE or PRIMARY KEY:

sql
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

This query finds duplicate email addresses. Either:
- Delete/merge duplicate rows if they shouldn't exist
- Use a different value for the current INSERT if this is a new record
- Check application logic for why duplicates are being generated

3Verify foreign key references exist before inserting child records

If the error involves a foreign key constraint:

sql
-- Check if referenced parent record exists
SELECT * FROM customers WHERE id = 999;

-- If not found, either:
-- 1. Create the parent record first
INSERT INTO customers (id, name) VALUES (999, 'New Customer');

-- 2. Or use an existing parent ID
INSERT INTO orders (customer_id, amount) VALUES (1, 100);

Always insert parent records before child records within a transaction.

4Use deferrable constraints to allow temporary violations

For complex transactions that must violate constraints temporarily, define constraints as deferrable:

sql
-- Recreate the constraint to be deferrable
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;

ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  DEFERRABLE INITIALLY DEFERRED;

Now constraint checks happen at commit time, not immediately:

sql
BEGIN;
  INSERT INTO orders (customer_id, amount) VALUES (999, 100);
  INSERT INTO customers (id, name) VALUES (999, 'New Customer');
COMMIT; -- Constraints checked here, both records exist

Note: DEFERRABLE INITIALLY IMMEDIATE (the default) checks constraints immediately. DEFERRABLE INITIALLY DEFERRED defers until commit.

5Handle violations in application code with ON CONFLICT or exception handling

For INSERT/UPDATE operations that might fail:

sql
-- Use ON CONFLICT for UNIQUE constraint violations
INSERT INTO users (email, name) VALUES ('user@example.com', 'Alice')
  ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

In PL/pgSQL functions, catch violations:

sql
CREATE FUNCTION safe_insert_order(p_customer_id INT, p_amount DECIMAL) RETURNS void AS $$
BEGIN
  INSERT INTO orders (customer_id, amount) VALUES (p_customer_id, p_amount);
EXCEPTION WHEN foreign_key_violation THEN
  RAISE EXCEPTION 'Customer % does not exist', p_customer_id;
END;
$$ LANGUAGE plpgsql;

Application drivers can also use savepoints to catch and recover from constraint violations:

python
# Python example with savepoints
try:
    cursor.execute("SAVEPOINT sp1")
    cursor.execute("INSERT INTO users (email) VALUES (%s)", (email,))
except IntegrityError:
    cursor.execute("ROLLBACK TO SAVEPOINT sp1")
    # Handle duplicate, try different value, etc.
6Review CHECK constraint rules if values are outside allowed range

If the error mentions a CHECK constraint:

sql
SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE table_name = 'products';

Common CHECK violations:

sql
-- This might have a CHECK (price > 0) constraint
INSERT INTO products (name, price) VALUES ('Widget', -10); -- FAILS

-- Use valid values
INSERT INTO products (name, price) VALUES ('Widget', 10); -- OK

Adjust your values to satisfy the constraint rules.

Advanced notes

PostgreSQL's strict transaction behavior differs from some databases. It provides ACID guarantees by aborting the entire transaction on any constraint violation. This has performance benefits: rollback is instant because no changes are written. Use savepoints in application drivers for fine-grained error recovery—PgJDBC supports ON_ERROR_ROLLBACK=true to create implicit savepoints. For interactive SQL, psql's \set ON_ERROR_ROLLBACK on prevents a typo from losing all work. The "current transaction is aborted" error appears after the actual constraint violation; you must ROLLBACK or COMMIT to clear this state. Deferred constraints add a small performance cost at commit time but enable cleaner application logic when transactions must temporarily violate constraints.

Frequently asked questions

What does "Transaction integrity constraint violation" mean?
A transaction integrity constraint violation occurs when an INSERT, UPDATE, or DELETE operation attempts to violate a database constraint. PostgreSQL has strict transactional semantics: when any constraint is violated, the entire transaction is automatically aborted and rolled back. This is different from some databases that allow partial failure recovery.
How do I fix "Transaction integrity constraint violation"?
PostgreSQL aborts transactions when data violates constraints like unique keys, foreign keys, or CHECK rules. Learn to identify which constraint failed, fix data conflicts, and use deferrable constraints to prevent transaction rollbacks.
What causes "Transaction integrity constraint violation"?
Inserting duplicate values into a PRIMARY KEY or UNIQUE constraint column Inserting child records with foreign key references that do not exist in parent table Updating a record with values that violate an existing constraint
Is the "Transaction integrity constraint violation" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading