Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A DELETE is refused because another table still points at the row

ErrorERROR: update or delete on table "tablename" violates foreign key constraint

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

  • Applies to PostgreSQL 16, PostgreSQL 17, Prisma ORM 6
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2Every 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. 3The constraint already uses SET NULL or SET DEFAULT and the delete still fails.SET NULL or SET DEFAULT cannot satisfy the constraintFix
    4. 4The 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

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

2Fix

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

Confirmed by prisma.io

Did this fix it?

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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.