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.
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.
The error message will specify the constraint name and the referencing table. For example:
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.
Find all child records that reference the parent row you're trying to delete or update:
-- 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.
The safest approach is to clean up child records before modifying the parent:
-- 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.
If you want to preserve child records, reassign them to another parent row:
-- 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.
If you frequently delete or modify parent rows, consider altering the constraint. Only do this if automatic deletion or nullification is the correct behavior:
-- 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:
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.
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.
-- 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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL