How to Fix PostgreSQL Error: Violates Foreign Key Constraint

The "Violates foreign key constraint" error occurs when an operation would break referential integrity between tables. This happens when inserting/updating a row that references a non-existent foreign key value, or when deleting/updating a row that is referenced by other rows.

Impact

This error prevents data modifications that would create orphaned records or break relationships between tables. It ensures data consistency but can cause application failures if not properly handled, particularly during data imports or when deleting related records.

Common Causes

Inserting row with foreign key value that doesn't exist in referenced table
Deleting parent row that has dependent child rows
Updating foreign key to non-existent value
Data loaded in wrong order (children before parents)
Cascading deletes not configured
Application logic not handling relationships correctly
Missing records in referenced table after partial data restore

Troubleshooting and Resolution Steps

Identify the constraint and missing reference:

-- Error example: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
-- Detail: Key (customer_id)=(123) is not present in table "customers".

-- Check if referenced record exists
SELECT * FROM customers WHERE id = 123;

-- View foreign key definition
\d orders

-- Or query constraints
SELECT conname, conrelid::regclass AS table_name,
       confrelid::regclass AS referenced_table,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'orders'::regclass;

Create missing parent record first:

-- Insert parent before child
INSERT INTO customers (id, name, email)
VALUES (123, 'John Doe', 'john@example.com');

-- Then insert child
INSERT INTO orders (id, customer_id, total)
VALUES (1, 123, 99.99);

Handle delete with cascading:

-- Add cascade on delete
ALTER TABLE orders
DROP CONSTRAINT orders_customer_id_fkey,
ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON DELETE CASCADE;  -- Deletes child rows when parent is deleted

-- Other options:
-- ON DELETE SET NULL (sets FK to NULL)
-- ON DELETE SET DEFAULT (sets FK to default value)
-- ON DELETE RESTRICT (prevents delete, default)
-- ON DELETE NO ACTION (same as RESTRICT)

-- Now deleting customer also deletes their orders
DELETE FROM customers WHERE id = 123;

Delete child records before parent:

-- Manual cascade delete
BEGIN;
DELETE FROM orders WHERE customer_id = 123;
DELETE FROM customers WHERE id = 123;
COMMIT;

-- Or use CTE for atomic operation
WITH deleted_orders AS (
    DELETE FROM orders WHERE customer_id = 123 RETURNING id
)
DELETE FROM customers WHERE id = 123;

Update foreign key with care:

-- Ensure new value exists before update
UPDATE orders
SET customer_id = 456
WHERE id = 1
AND EXISTS (SELECT 1 FROM customers WHERE id = 456);

-- Or use ON UPDATE CASCADE
ALTER TABLE orders
DROP CONSTRAINT orders_customer_id_fkey,
ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON UPDATE CASCADE;  -- Updates child FKs when parent PK changes

Allow NULL foreign keys:

-- Make foreign key nullable
ALTER TABLE orders ALTER COLUMN customer_id DROP NOT NULL;

-- Now you can set to NULL
UPDATE orders SET customer_id = NULL WHERE customer_id = 123;
DELETE FROM customers WHERE id = 123;

Find orphaned records:

-- Find orders with non-existent customers
SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL AND o.customer_id IS NOT NULL;

-- Clean up orphaned records
DELETE FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers)
AND customer_id IS NOT NULL;

Temporarily disable constraints (use with caution):

-- Drop constraint temporarily
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;

-- Perform data operations
-- ... your operations here ...

-- Re-add constraint
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id)
    REFERENCES customers(id);

-- Note: This will fail if there are now invalid references
-- Use NOT VALID to defer validation
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id)
    REFERENCES customers(id) NOT VALID;

-- Validate later
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

Bulk data loading strategy:

-- Load in correct order (parents first)
COPY customers FROM '/path/customers.csv' CSV;
COPY orders FROM '/path/orders.csv' CSV;

-- Or disable triggers temporarily (superuser only)
ALTER TABLE orders DISABLE TRIGGER ALL;
COPY orders FROM '/path/orders.csv' CSV;
ALTER TABLE orders ENABLE TRIGGER ALL;

-- Then clean up orphaned records
DELETE FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers);

Application-level handling:

# Python with psycopg2
from psycopg2 import IntegrityError

try:
    cursor.execute(
        "INSERT INTO orders (customer_id, total) VALUES (%s, %s)",
        (123, 99.99)
    )
    conn.commit()
except IntegrityError as e:
    conn.rollback()
    if 'foreign key constraint' in str(e):
        # Handle missing parent - maybe create it first
        cursor.execute(
            "INSERT INTO customers (id, name) VALUES (%s, %s)",
            (123, 'Unknown Customer')
        )
        # Retry original insert
        cursor.execute(
            "INSERT INTO orders (customer_id, total) VALUES (%s, %s)",
            (123, 99.99)
        )
        conn.commit()

Additional Information

Foreign keys enforce referential integrity automatically
CASCADE options automate relationship management
NULL foreign keys don't violate constraints
Composite foreign keys must match all columns
Foreign keys can reference unique constraints, not just primary keys
Deferrable constraints can be checked at transaction end instead of immediately
Consider using soft deletes instead of cascading deletes for audit trails

Frequently Asked Questions

Q: What's the difference between ON DELETE CASCADE and ON DELETE SET NULL?
A: CASCADE deletes child rows when parent is deleted. SET NULL keeps child rows but sets the foreign key column to NULL.

Q: Can a foreign key reference a non-primary key column?
A: Yes, it can reference any UNIQUE column or set of columns, not just primary keys.

Q: How do I create a self-referencing foreign key?
A: Reference the same table: ALTER TABLE employees ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(id);

Q: What are deferrable constraints?
A: Deferrable constraints can be checked at transaction commit instead of immediately:

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

Q: Can I have multiple foreign keys in one table?
A: Yes, a table can have foreign keys to multiple other tables, and multiple foreign keys to the same table.

Q: Why use foreign keys instead of application-level checks?
A: Database-level foreign keys guarantee integrity regardless of which application or tool modifies the data. They're enforced at the lowest level and can't be bypassed.

Q: How do I find all tables that reference a specific table?
A: Use:

SELECT confrelid::regclass AS referencing_table,
       conname AS constraint_name
FROM pg_constraint
WHERE confrelid::regclass::text LIKE '%table_name%'
AND contype = 'f';