PostgreSQL 23001 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 23001: restrict_violation Explained

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 (or default NO ACTION) option. Unlike CASCADE, the RESTRICT option instructs PostgreSQL to immediately block the operation and raise this error to protect referential integrity. Understanding this error is essential for any developer or DBA working with relational data models in PostgreSQL.

Top 3 Causes

1. Deleting a Parent Row That Is Still Referenced

The most common scenario. When a foreign key is created without an explicit ON DELETE option, PostgreSQL defaults to NO ACTION, which behaves identically to RESTRICT in most cases.

-- Setup
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) -- defaults to RESTRICT
);

INSERT INTO customers VALUES (1, 'Alice');
INSERT INTO orders VALUES (1, 1);

-- This will raise ERROR 23001
DELETE FROM customers WHERE customer_id = 1;
-- ERROR:  update or delete on table "customers" violates foreign key
-- constraint "orders_customer_id_fkey" on table "orders"

2. Updating a Referenced Primary Key Value

If you attempt to change the primary key value of a parent row while child rows reference the old value, and ON UPDATE RESTRICT is in effect, PostgreSQL will block the update.

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

3. Hidden Intermediate Table Blocking Cascaded Deletes

In schemas with multi-level relationships, a RESTRICT constraint on an intermediate table can silently block what appears to be a straightforward top-level delete.

-- Three-level hierarchy
CREATE TABLE departments (dept_id SERIAL PRIMARY KEY);
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    dept_id INT REFERENCES departments(dept_id) ON DELETE RESTRICT
);
CREATE TABLE salaries (
    salary_id SERIAL PRIMARY KEY,
    emp_id INT REFERENCES employees(emp_id) ON DELETE CASCADE
);

-- Deleting department will fail even if salaries cascade,
-- because employees still reference the department
DELETE FROM departments WHERE dept_id = 1;
-- ERROR 23001 raised by the employees -> departments FK

Quick Fix Solutions

Option 1: Delete child rows first

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

Option 2: Change the foreign key to CASCADE

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;

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

Option 3: Use DEFERRABLE constraints for complex update scenarios

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;

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

Audit all foreign keys and their current rules:

SELECT
    tc.table_name AS child_table,
    ccu.table_name AS parent_table,
    rc.delete_rule,
    rc.update_rule
FROM information_schema.table_constraints tc
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';

Prevention Tips

Always explicitly define ON DELETE and ON UPDATE options when creating foreign keys. Never rely on the default behavior. Decide upfront whether child data should be cascaded, nullified, or left in place when a parent is modified, and document this decision in your schema migration files.
Encapsulate delete/update logic in stored procedures or application service layers to enforce the correct operation order. This prevents ad-hoc SQL from accidentally violating referential integrity and makes auditing much easier.

Related Error Codes

23000 – integrity_constraint_violation: Parent class of 23001.
23503 – foreign_key_violation: Raised on INSERT/UPDATE in child table referencing a non-existent parent key.
23505 – unique_violation: Raised on duplicate key inserts.
23502 – not_null_violation: Raised when a NOT NULL column receives a null value.

📖 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.

DEV Community