🚨 Postgres Error: 'cannot drop ... because other objects depend on it' — Cause, Fix, and Troubleshooting Guide

Overview

PostgreSQL refuses to drop an object when another object still depends on it. Rather than silently orphan a foreign key, view, or default expression, it aborts the DROP and tells you exactly what is in the way.

ERROR:  cannot drop table customers because other objects depend on it
DETAIL:  constraint orders_customer_id_fkey on table orders depends on table customers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The DETAIL line is the whole story: it names the dependent object (orders_customer_id_fkey) and where it lives (table orders). The default DROP behaviour is RESTRICT, which is why Postgres stops instead of cascading. The same error shape appears for tables, columns, types, sequences, and functions.

Symptoms

• A DROP TABLE, DROP TYPE, DROP SEQUENCE, or ALTER TABLE ... DROP COLUMN fails with cannot drop ... because other objects depend on it.
• The DETAIL block lists one or more dependent constraints, views, or functions.
• The drop works fine in an empty scratch schema but fails in the real schema where relationships exist.

DROP TABLE customers;

ERROR:  cannot drop table customers because other objects depend on it
DETAIL:  constraint orders_customer_id_fkey on table orders depends on table customers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Common Root Causes

1. Foreign keys reference the object

A child table holds a foreign key pointing at the table (or column) you are trying to drop.

SELECT conname, conrelid::regclass AS child_table
FROM pg_constraint
WHERE confrelid = 'customers'::regclass
  AND contype = 'f';

        conname         | child_table
------------------------+-------------
 orders_customer_id_fkey | orders
(1 row)

2. Views or materialized views are built on it

A view that selects from the table depends on it, and so does everything selecting from that view (dependencies chain).

SELECT dependent.relname AS view_name, dependent.relkind
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class dependent ON dependent.oid = r.ev_class
WHERE d.refobjid = 'customers'::regclass
  AND dependent.relkind IN ('v', 'm');

3. Functions, sequences, or default expressions depend on it

A column default of nextval('customers_id_seq'), or a function with the table in its signature or body, creates a dependency that blocks the drop.

How to diagnose

Step 1: Read the DETAIL lines first

Every dependent object is listed in the DETAIL block. For a big object the list can be long — run the drop once and let Postgres enumerate the blockers for you.

DROP TABLE customers;
-- read every DETAIL line; each names one dependent object to deal with

Step 2: Query pg_depend for the full dependency set

pg_depend records dependencies as OID pairs; join it back to pg_class and pg_constraint to make them human-readable.

SELECT DISTINCT
       c.relname   AS dependent_object,
       con.conname AS constraint_name
FROM pg_depend d
LEFT JOIN pg_constraint con ON con.oid = d.objid
LEFT JOIN pg_class c        ON c.oid = con.conrelid
WHERE d.refobjid = 'customers'::regclass;

Step 3: List the referencing foreign keys directly

For the common foreign-key case, pg_constraint gives a clean list of children to repoint or drop.

SELECT conname,
       conrelid::regclass  AS child_table,
       confrelid::regclass AS parent_table
FROM pg_constraint
WHERE confrelid = 'customers'::regclass
  AND contype = 'f';

Fixes

Drop just the blocking constraint

If only a foreign key is in the way and you intend to keep the child table, drop the constraint and leave the data alone.

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
DROP TABLE customers;

Drop the dependents explicitly, in order

The safest destructive path is to drop each dependent object yourself so nothing disappears by surprise.

DROP VIEW IF EXISTS active_customers;
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
DROP TABLE customers;

Use DROP … CASCADE — with caution

CASCADE drops the object and everything that depends on it in one statement. It is convenient but destructive: it will silently remove views and constraints you may not have listed.

DROP TABLE customers CASCADE;

NOTICE:  drop cascades to constraint orders_customer_id_fkey on table orders
DROP TABLE

Read the NOTICE: drop cascades to ... lines it prints — that is your record of what CASCADE just destroyed.

What to watch out for

• CASCADE can silently drop views and materialized views built on the object; you lose their definitions, not just the dependency. Script them out first.
• Run destructive drops inside a transaction (BEGIN; ... ROLLBACK;) to preview the NOTICE: drop cascades list before committing.
• Dropping a constraint with ALTER TABLE ... DROP CONSTRAINT removes referential integrity — the child data stays but is no longer protected.
• Dependencies chain: a view on a view on the table means CASCADE reaches further than the single DETAIL line suggests.
• DROP ... RESTRICT is the default; being explicit documents intent and prevents an accidental cascade in a migration script.

Related

• Postgres Error: ‘there is no unique constraint matching given keys’
• Postgres Error: ‘relation does not exist’
• Postgres Error: ‘permission denied for table’