A DELETE is refused because another table still points at the row
ERROR: update or delete on table "tablename" violates foreign key constraintShort answer
PostgreSQL refuses the delete because at least one row in another table still references the row you are removing. Find the foreign key that points at the table, then either delete the child rows first or change the constraint's ON DELETE action to CASCADE or SET NULL. Prisma users hit the same wall through its default Restrict action.
Fix it fast
1Find out which cause you have
List every foreign key in the database with its ON DELETE action. The row whose referenced_table is the table you are deleting from is the one blocking you.
SELECT c.conname, c.conrelid::regclass AS referencing_table, c.confrelid::regclass AS referenced_table, CASE c.confdeltype WHEN 'a' THEN 'no action' WHEN 'r' THEN 'restrict' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' END AS on_delete FROM pg_constraint AS c WHERE c.contype = 'f';2Match what you see, then jump to its fix
- 1
The constraint shows on_delete = no action or restrict, and a query on the child table finds rows with the parent's key.Child rows still exist and nothing was told to remove themFix - 2
Every delete of a parent needs the same manual clean-up, and children have no meaning without the parent.The constraint should remove or detach children automatically, but it does notFix - 3
The constraint already uses SET NULL or SET DEFAULT and the delete still fails.SET NULL or SET DEFAULT cannot satisfy the constraintFix - 4
The delete comes from prisma.<model>.delete(...) and the relation field in schema.prisma is not optional.Prisma's default onDelete is Restrict on a required relationFix
- 1
What does the error look like?
ERROR: update or delete on table "tablename" violates foreign key constraint
Cause 1 of 4
Child rows still exist and nothing was told to remove them
1Check
With the default NO ACTION the delete may proceed in principle, but the constraint must still hold at the end, so it usually ends in this error. RESTRICT refuses at once. Look for the child rows.
SELECT * FROM orders WHERE product_no = 5;2Fix
Delete or re-point the child rows first, in the same transaction as the parent delete.
BEGIN;
DELETE FROM orders WHERE product_no = 5;
DELETE FROM products WHERE product_no = 5;
COMMIT;3Verify
The parent delete reports DELETE 1 and no error.
SELECT count(*) FROM products WHERE product_no = 5;Confirmed by postgresql.org
Cause 2 of 4
The constraint should remove or detach children automatically, but it does not
1Check
In the first-step output, the constraint's on_delete is no action or restrict, not cascade or set null.
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE contype = 'f' AND confrelid = 'products'::regclass;2Fix
Drop the constraint and add it back with the action you want. CASCADE deletes the child rows too, so use it only when that is really what you mean; SET NULL needs a nullable column.
ALTER TABLE orders DROP CONSTRAINT orders_product_no_fkey;
ALTER TABLE orders
ADD CONSTRAINT orders_product_no_fkey
FOREIGN KEY (product_no) REFERENCES products (product_no)
ON DELETE CASCADE;3Verify
Delete a test parent row, then count its children. Both are gone.
DELETE FROM products WHERE product_no = 5; SELECT count(*) FROM orders WHERE product_no = 5;Confirmed by postgresql.org
Cause 3 of 4
SET NULL or SET DEFAULT cannot satisfy the constraint
1Check
SET DEFAULT only works if the column's default value points at a row that exists. SET NULL only works on a column that allows NULL. Check both on the child table.
SELECT column_name, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'orders' AND column_name = 'product_no';2Fix
Make the column nullable, give it a default that references a real row, or switch the action to CASCADE.
ALTER TABLE orders ALTER COLUMN product_no DROP NOT NULL;3Verify
Repeat the delete that failed.
DELETE FROM products WHERE product_no = 5;Confirmed by postgresql.org
Cause 4 of 4
Prisma's default onDelete is Restrict on a required relation
1Check
For a mandatory relation with no explicit action, Prisma uses Restrict on delete; for an optional relation it uses SetNull. Open the model and read the relation.
grep -n "@relation" prisma/schema.prisma2Fix
Set onDelete on the relation. Cascade removes the children, and SetNull works only when the relation field is optional (author User? with authorId Int?).
model Post {
id Int @id @default(autoincrement())
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
authorId Int
}3Verify
Referential actions map to foreign key constraints in the database, so apply a migration with your usual Prisma workflow, then repeat the delete. Confirm the schema carries the action.
grep -n "onDelete" prisma/schema.prismaConfirmed by prisma.io
How do I stop it coming back?
- Decide the ON DELETE action when you create each foreign key instead of leaving the default.
- Delete parent and children in one transaction so a failure leaves nothing half removed.
- Prefer SET NULL or a soft-delete column when child rows must outlive their parent.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
A PostgreSQL restore fails on foreign key constraints
data-only restore into a schema that already has foreign keys fails
Foreign keys fail on restore when rows load before the rows they point to, or when the dump never contained the parent tables.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix