How to fix Restrict violation in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 217 views

A RESTRICT violation occurs when you try to delete or update a parent table row that is still referenced by rows in a child table with a RESTRICT foreign key constraint. The fix requires handling the referencing child rows first or modifying the constraint definition.

Quick fix

Fixes 23001

ERROR: update or update on table "customers" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (id)=(42) is still referenced from table "orders".
  1. Identify which table and constraint is causing the violation
  2. Query the child table to see all referencing rows
  3. Option A: Delete or update child rows first
  4. Option B: Reassign child rows to a different parent
postgresql — error
ERROR: update or update on table "customers" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (id)=(42) is still referenced from table "orders".
Restrict violation

What this error means

A RESTRICT constraint is a type of referential integrity rule in PostgreSQL that prevents deletion or modification of a parent row if child rows still reference it. Unlike CASCADE (which deletes children automatically) or SET NULL (which nullifies the reference), RESTRICT enforces strict referential integrity by blocking the operation entirely. The error code 23001 (SQLSTATE) indicates this constraint violation. RESTRICT is immediately enforced and cannot be deferred within a transaction, unlike NO ACTION which allows deferred checking.

Typical symptoms

  • DELETE statements fail on parent tables with a 23001 error code
  • UPDATE statements fail when trying to modify primary key values in referenced parent rows
  • Error message includes the constraint name and the table that has child references
  • Operations fail even if you try to defer constraints within a transaction

Common causes

  1. Attempting to delete a parent row while child rows still reference it via a RESTRICT foreign key
  2. Attempting to update a primary key value in a parent table when child rows reference the old value
  3. Using ON DELETE RESTRICT or ON UPDATE RESTRICT on a foreign key constraint when related child data exists
  4. Not accounting for all tables that reference a parent table through foreign keys

How to fix "Restrict violation"

1Identify which table and constraint is causing the violation

The error message will specify the constraint name and the referencing table. For example:

bash
ERROR: update or update on table "customers" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (id)=(42) is still referenced from table "orders".

Note the parent table (customers), the child table (orders), and the specific key value. This tells you exactly what data is blocking your operation.

2Query the child table to see all referencing rows

Find all child records that reference the parent row you're trying to delete or update:

sql
-- Example: find all orders referencing customer 42
SELECT * FROM orders WHERE customer_id = 42;

This shows you what data exists and helps you decide the best fix approach.

3Option A: Delete or update child rows first

The safest approach is to clean up child records before modifying the parent:

sql
-- Delete all child records referencing this parent
DELETE FROM orders WHERE customer_id = 42;

-- Now you can delete or modify the parent
DELETE FROM customers WHERE id = 42;

-- Or update the parent
UPDATE customers SET id = 43 WHERE id = 42;

This preserves data integrity and is preferred when child records should not automatically be deleted.

4Option B: Reassign child rows to a different parent

If you want to preserve child records, reassign them to another parent row:

sql
-- Reassign all orders to a different customer
UPDATE orders SET customer_id = 999 WHERE customer_id = 42;

-- Now you can safely delete the original parent
DELETE FROM customers WHERE id = 42;

This is useful when consolidating records or migrating data.

5Option C: Change the constraint to CASCADE or SET NULL

If you frequently delete or modify parent rows, consider altering the constraint. Only do this if automatic deletion or nullification is the correct behavior:

sql
-- Drop the restrictive constraint
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;

-- Recreate with CASCADE (deletes child rows automatically)
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;

Or use SET NULL to nullify the foreign key instead of deleting:

sql
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) ON DELETE SET NULL;

Only use CASCADE if child records should always be deleted when their parent is deleted.

Advanced notes

RESTRICT vs NO ACTION: Both prevent deletion, but there's a key difference. RESTRICT is immediately enforced during the operation, while NO ACTION can be deferred to the end of a transaction. If you use a DEFERRABLE constraint with INITIALLY DEFERRED, NO ACTION allows you to reorder operations within a transaction—RESTRICT does not.

sql
-- NO ACTION with deferral - allows reordering within transaction
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;

-- RESTRICT - always fails immediately, cannot defer
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT;

ON UPDATE RESTRICT: This is particularly strict. Even if you update a parent's primary key to a value that would still satisfy the constraint, RESTRICT will block it. NO ACTION with deferred checking would allow it if the final state is valid.

Error Code 23001: This SQLSTATE makes it easy to catch in application-level error handling.

Data Migration Strategy: For large migrations with many interdependencies, use transactions with deferred constraint checking (if using NO ACTION), or script the operations in dependency order.

Frequently asked questions

What does "23001" mean?
A RESTRICT constraint is a type of referential integrity rule in PostgreSQL that prevents deletion or modification of a parent row if child rows still reference it. Unlike CASCADE (which deletes children automatically) or SET NULL (which nullifies the reference), RESTRICT enforces strict referential integrity by blocking the operation entirely. The error code 23001 (SQLSTATE) indicates this constraint violation.
How do I fix "23001"?
A RESTRICT violation occurs when you try to delete or update a parent table row that is still referenced by rows in a child table with a RESTRICT foreign key constraint. The fix requires handling the referencing child rows first or modifying the constraint definition.
What causes "23001"?
Attempting to delete a parent row while child rows still reference it via a RESTRICT foreign key Attempting to update a primary key value in a parent table when child rows reference the old value Using ON DELETE RESTRICT or ON UPDATE RESTRICT on a foreign key constraint when related child data exists
Is the "23001" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading