PostgreSQL 23001 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 23001: restrict_violation

PostgreSQL error code 23001, restrict_violation, occurs when you attempt to delete or update a row in a parent table that is still being referenced by one or more rows in a child table, and the foreign key constraint is defined with the RESTRICT option. Unlike NO ACTION, RESTRICT enforces the constraint check immediately within the current statement, even inside a transaction. This is a deliberate data integrity safeguard built into PostgreSQL to prevent orphaned records.

Top 3 Causes

1. Deleting a Parent Row While Child References Exist

The most common cause is attempting to delete a parent record when child records still hold a foreign key reference to it.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

INSERT INTO customers (name) VALUES ('Alice');
INSERT INTO orders (customer_id) VALUES (1);

-- This will raise ERROR 23001
DELETE FROM customers WHERE customer_id = 1;
-- ERROR: update or delete on table "customers" violates foreign key
-- constraint "fk_customer" on table "orders"
-- DETAIL: Key (customer_id)=(1) is still referenced from table "orders".

2. Updating a Referenced Parent Key Value

When ON UPDATE RESTRICT is set, updating the primary key of a parent row that is referenced by child rows will trigger this error.

-- This will also raise ERROR 23001
UPDATE customers SET customer_id = 999 WHERE customer_id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint

3. Wrong Deletion Order in Application Logic

Batch jobs, migration scripts, or ORM frameworks that delete parent rows before child rows will consistently hit this error.

-- Wrong order - will fail
DELETE FROM customers WHERE customer_id = 1; -- ERROR 23001

-- Correct order - delete children first
BEGIN;
    DELETE FROM orders WHERE customer_id = 1;
    DELETE FROM customers WHERE customer_id = 1;
COMMIT;

Quick Fix Solutions

Fix 1: Delete child records first in a transaction

BEGIN;
    DELETE FROM orders WHERE customer_id = 1;
    DELETE FROM customers WHERE customer_id = 1;
COMMIT;

Fix 2: Change the foreign key to CASCADE if business logic allows

-- Drop the RESTRICT constraint and replace with CASCADE
ALTER TABLE orders DROP CONSTRAINT fk_customer;

ALTER TABLE orders
    ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE;

-- Now this works automatically
DELETE FROM customers WHERE customer_id = 1;

Fix 3: Find all tables referencing a parent table before deletion

-- Identify which tables reference 'customers'
SELECT
    tc.table_name AS child_table,
    kcu.column_name AS child_column,
    rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints rc
    ON rc.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND ccu.table_name = 'customers';

Prevention Tips

1. Define explicit foreign key policies during schema design

Always explicitly declare ON DELETE and ON UPDATE behavior when creating foreign keys. Never rely on defaults without understanding the implications. Document the chosen strategy for each relationship so the whole team follows consistent patterns.

-- Be explicit about your referential action
ALTER TABLE orders
    ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE RESTRICT   -- or CASCADE, SET NULL, SET DEFAULT
    ON UPDATE RESTRICT;

2. Add pre-deletion validation in batch jobs and migrations

Before executing bulk deletes, always run a reference check query to detect potential violations early — before they cause runtime failures in production.

-- Pre-check before deletion
SELECT COUNT(*) AS blocking_references
FROM orders
WHERE customer_id = 1;

-- Only proceed if count = 0, or handle child records first

Related Errors

23000 – integrity_constraint_violation: Parent class of all integrity errors including 23001.
23503 – foreign_key_violation: The reverse scenario — inserting a child row that references a non-existent parent key.
23505 – unique_violation: Triggered when a duplicate value violates a UNIQUE or PRIMARY KEY constraint.
23514 – check_violation: Raised when an inserted or updated value fails a CHECK constraint condition.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.