How to fix Integrity constraint violation in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 26, 2025· 218 views

PostgreSQL error 23000 (Integrity Constraint Violation) occurs when an INSERT, UPDATE, or DELETE operation breaks a primary key, foreign key, unique, or check constraint. This is a parent error class that includes more specific violations like 23505 (unique violation) and 23503 (foreign key violation). Fixing requires identifying the specific constraint, validating referenced data, or using ON CONFLICT clauses to handle conflicts gracefully.

Quick fix

Fixes 23000

-- Find all constraints on a table:
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'your_table';

-- Find columns involved in a specific constraint:
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_name = 'your_table' AND constraint_name = 'constraint_name';

-- Find foreign key relationships:
SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.referential_constraints
WHERE table_name = 'your_table';
  1. Identify the specific constraint type from the error message
  2. For foreign key violations, verify the referenced row exists
  3. For unique/primary key violations, check for duplicates
  4. For NOT NULL violations, provide required values
postgresql — error
-- Find all constraints on a table:
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'your_table';
-- Find columns involved in a specific constraint:
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_name = 'your_table' AND constraint_name = 'constraint_name';
-- Find foreign key relationships:
SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.referential_constraints
WHERE table_name = 'your_table';
Integrity constraint violation

What this error means

PostgreSQL Error 23000 (Integrity Constraint Violation) is a broad error class that covers any constraint violation at the database level. Unlike application-level validation, PostgreSQL enforces these constraints directly on the data before writing to disk, ensuring data integrity even if buggy code attempts to bypass safeguards. The error typically manifests with more specific SQLSTATE codes: - SQLSTATE 23505: Unique Constraint Violation (duplicate key) - SQLSTATE 23503: Foreign Key Constraint Violation (referential integrity) - SQLSTATE 23514: Check Constraint Violation (invalid value) - SQLSTATE 23502: Not-Null Violation (missing required value) When you see "Integrity constraint violation," PostgreSQL is protecting your data by refusing to allow an operation that would corrupt consistency. Understanding the specific constraint type (primary key, foreign key, unique, or check) is essential to fixing the underlying issue. PostgreSQL validates constraints at the moment of statement execution. If a violation is detected, the entire statement is aborted. If the statement is part of a transaction and occurs outside a savepoint, the entire transaction is rolled back, requiring you to retry or handle the conflict differently.

Typical symptoms

  • INSERT operation fails with an integrity constraint violation message
  • UPDATE operation fails when changing a value that conflicts with a constraint
  • DELETE operation fails because child records depend on the row being deleted (foreign key)
  • Application receives error code 23000, 23505, 23503, 23514, or 23502 from the database driver
  • Bulk data import or migration fails partway through
  • Orphaned records exist in a child table whose parent was deleted
  • NULL value is being inserted into a NOT NULL column
  • CHECK constraint prevents inserting values outside the allowed range

Common causes

  1. Foreign key constraint violated: inserting a reference to a non-existent row in the parent table
  2. Unique constraint violated: attempting to insert or update a value that already exists
  3. Primary key violated: duplicate or NULL primary key values
  4. Not-null constraint violated: attempting to insert or update NULL in a NOT NULL column
  5. Check constraint violated: inserting a value outside the defined range or rule
  6. Bulk insert contains orphaned references to rows that don't exist in the parent table
  7. Migrating data from another system where constraint rules differ
  8. Foreign key ON DELETE/UPDATE actions not properly handling cascading deletes

How to fix "Integrity constraint violation"

1Identify the specific constraint type from the error message

The error message will hint at which constraint is violated. Use PostgreSQL system tables to find the exact constraint.

sql
-- Find all constraints on a table:
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'your_table';

-- Find columns involved in a specific constraint:
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_name = 'your_table' AND constraint_name = 'constraint_name';

-- Find foreign key relationships:
SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.referential_constraints
WHERE table_name = 'your_table';

Note the constraint type (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) and which columns are involved. This determines your next step.

2For foreign key violations, verify the referenced row exists

Foreign key constraints (error 23503) require that any referenced value exists in the parent table.

sql
-- Check if the value exists in the referenced table:
SELECT * FROM parent_table WHERE id = <referenced_value>;

-- For the operation you're trying:
-- If it doesn't exist, you have two options:

-- Option 1: Insert the missing parent row first
INSERT INTO parent_table (id, name) VALUES (<value>, 'Parent Name');

-- Then retry your original INSERT:
INSERT INTO child_table (parent_id, data) VALUES (<value>, 'Child Data');

-- Option 2: Insert with a valid parent ID that exists:
SELECT id FROM parent_table LIMIT 5; -- Find a valid parent ID
-- Use one of these valid IDs in your INSERT

Always ensure the parent row exists before inserting child rows.

3For unique/primary key violations, check for duplicates

Unique constraints (error 23505) prevent duplicate values. Check if the value already exists.

sql
-- Find duplicates in the target column:
SELECT column_name, COUNT(*) as count
FROM your_table
GROUP BY column_name
HAVING COUNT(*) > 1;

-- Check if the specific value you're inserting exists:
SELECT * FROM your_table WHERE unique_column = 'value';

-- If it exists:
-- Option 1: Use a different value that is unique
-- Option 2: Update the existing row instead of inserting
UPDATE your_table SET column = 'new_value' WHERE unique_column = 'value';

-- Option 3: Use ON CONFLICT to skip or update:
INSERT INTO your_table (unique_column, data)
VALUES ('value', 'data')
ON CONFLICT (unique_column) DO NOTHING;

If duplicates exist in the table itself, you must delete them before enforcing the constraint.

4For NOT NULL violations, provide required values

NOT NULL constraints (error 23502) require a value in the column.

sql
-- Check which columns are NOT NULL:
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'your_table' AND is_nullable = 'NO';

-- Ensure your INSERT provides values for all NOT NULL columns:
INSERT INTO your_table (id, required_column, optional_column)
VALUES (1, 'value', NULL); -- required_column must have a value

-- If you're setting a column to NULL in UPDATE:
UPDATE your_table SET required_column = 'value' -- NOT NULL, so provide a value
WHERE id = 1;

-- If a column has a default, you can omit it from INSERT:
INSERT INTO your_table (id, name) -- status may have a DEFAULT
VALUES (1, 'name');

Always include values for NOT NULL columns, or ensure they have DEFAULT values defined.

5For CHECK constraint violations, verify the value is in range

CHECK constraints (error 23514) validate that values meet a condition.

sql
-- Find CHECK constraints on the table:
SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_name LIKE '%your_table%';

-- Or query the definition:
SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE relname = 'your_table' AND contype = 'c';

-- Verify your value meets the constraint:
-- Example: age >= 0 AND age <= 150
INSERT INTO users (name, age)
VALUES ('John', 25); -- Valid: 0 <= 25 <= 150

-- This would fail:
INSERT INTO users (name, age)
VALUES ('Jane', -5); -- Invalid: -5 < 0

Ensure all values being inserted or updated comply with the CHECK condition defined on the column.

6Use INSERT...ON CONFLICT to handle constraint violations gracefully

The ON CONFLICT clause prevents errors when duplicates are expected. Choose the appropriate action.

sql
-- For unique/primary key conflicts, skip the insert:
INSERT INTO users (id, email, name)
VALUES (1, 'john@example.com', 'John')
ON CONFLICT (email) DO NOTHING;

-- Or update the existing row (UPSERT):
INSERT INTO users (id, email, name)
VALUES (1, 'john@example.com', 'John Doe')
ON CONFLICT (email) DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = NOW();

-- For composite constraints:
INSERT INTO likes (user_id, post_id, created_at)
VALUES (5, 10, NOW())
ON CONFLICT (user_id, post_id) DO UPDATE SET
  created_at = NOW();

-- For primary key:
INSERT INTO users (id, email)
VALUES (1, 'john@example.com')
ON CONFLICT (id) DO NOTHING;

ON CONFLICT is essential for bulk inserts where duplicates are expected, preventing transaction rollback.

7For DELETE operations failing due to foreign keys, check CASCADE settings

If DELETE fails with a foreign key violation, child records reference the row you're trying to delete.

sql
-- Find which child tables reference this row:
SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.referential_constraints
WHERE referenced_table_name = 'parent_table';

-- Check if there are child records:
SELECT * FROM child_table WHERE parent_id = <id>;

-- Option 1: Delete the child records first:
DELETE FROM child_table WHERE parent_id = <id>;
DELETE FROM parent_table WHERE id = <id>;

-- Option 2: If the constraint has ON DELETE CASCADE, it should auto-delete:
-- Verify the constraint definition:
SELECT pg_get_constraintdef(oid) FROM pg_constraint
WHERE conname = 'constraint_name';

-- If not CASCADE, the parent row cannot be deleted while children exist.
-- Either keep the parent or delete children first.

Foreign key constraints prevent orphaned data. Delete dependents before the parent, or use ON DELETE CASCADE when defining the constraint.

Advanced notes

Integrity constraint violations in multi-step operations can be handled using PostgreSQL transactions with savepoints. If a constraint violation occurs in the middle of a transaction, you can ROLLBACK TO SAVEPOINT to undo just that step and retry with different values: BEGIN; SAVEPOINT sp1; INSERT...; ROLLBACK TO SAVEPOINT sp1; INSERT ... (different values); COMMIT;

For performance in bulk operations: if you're inserting thousands of rows and some are expected to violate uniqueness, use ON CONFLICT DO NOTHING instead of checking for existence first. This avoids the overhead of SELECT + INSERT and transaction rollback, making bulk loads much faster.

Deferred constraints allow you to temporarily violate constraints within a transaction, as long as they're satisfied by COMMIT time. Use: ALTER TABLE table_name ALTER CONSTRAINT constraint_name DEFERRABLE; Then: SET CONSTRAINTS ALL DEFERRED; at the start of your transaction. This is useful for circular dependencies where both tables must be updated before commit.

For debugging cascade behavior: enable query logging to see exactly which DELETE/UPDATE statements are being executed as part of CASCADE operations. The PostgreSQL log will show child deletes triggered by parent deletes, helping diagnose unexpected data loss.

Referential integrity actions (RESTRICT, CASCADE, SET NULL, SET DEFAULT) are defined on the foreign key constraint, not the data. You cannot change these actions at runtime; they must be defined when creating or altering the constraint. This is by design—consistency rules should not vary by operation.

Frequently asked questions

What does "23000" mean?
PostgreSQL Error 23000 (Integrity Constraint Violation) is a broad error class that covers any constraint violation at the database level. Unlike application-level validation, PostgreSQL enforces these constraints directly on the data before writing to disk, ensuring data integrity even if buggy code attempts to bypass safeguards. The error typically manifests with more specific SQLSTATE codes: - SQLSTATE 23505: Unique Constraint Violation (duplicate key) - SQLSTATE 23503: Foreign Key Constraint Violation (referential integrity) - SQLSTATE 23514: Check Constraint Violation (invalid value) - SQLSTATE 23502: Not-Null Violation (missing required value) When you see "Integrity constraint violation," PostgreSQL is protecting your data by refusing to allow an operation that would corrupt consistency.
How do I fix "23000"?
PostgreSQL error 23000 (Integrity Constraint Violation) occurs when an INSERT, UPDATE, or DELETE operation breaks a primary key, foreign key, unique, or check constraint. This is a parent error class that includes more specific violations like 23505 (unique violation) and 23503 (foreign key violation). Fixing requires identifying the specific constraint, validating referenced data, or using ON CONFLICT clauses to handle conflicts gracefully.
What causes "23000"?
Foreign key constraint violated: inserting a reference to a non-existent row in the parent table Unique constraint violated: attempting to insert or update a value that already exists Primary key violated: duplicate or NULL primary key values
Is the "23000" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading