restrict violation
SQLSTATE 23001 is raised when a DELETE or UPDATE is blocked by a RESTRICT foreign key constraint — a child row still references the row being deleted or updated in the parent table.
- 1Deleting or updating a parent row that has child rows referencing it under a RESTRICT foreign key
Deleting a parent row with existing child references.
DELETE FROM departments WHERE id = 1; -- employees table has rows referencing department_id = 1 with ON DELETE RESTRICT
expected output
ERROR: update or delete on table "departments" violates foreign key constraint "employees_department_id_fkey" on table "employees"
Fix 1
Delete child rows first, then delete the parent
WHEN When cascading deletion is appropriate.
DELETE FROM employees WHERE department_id = 1; DELETE FROM departments WHERE id = 1;
Why this works
Removing dependent rows before the parent satisfies the RESTRICT constraint.
Fix 2
Change the foreign key to ON DELETE CASCADE
WHEN When child rows should be automatically deleted with the parent.
ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey,
ADD CONSTRAINT employees_department_id_fkey
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE;Why this works
CASCADE automatically deletes child rows when the parent is deleted, eliminating the RESTRICT error.
✕ Drop the foreign key constraint to allow the delete
Removing the constraint leaves orphaned child rows with invalid references, corrupting referential integrity.
Class 23 — Integrity Constraint Violation
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev