cannot drop because other objects depend on it

What it means

PostgreSQL records which objects are built on which: a view on the tables it reads, a foreign key on the table it references, a column on its type, a default or generated column on the function it calls. It won’t drop an object that something else still needs, because that would leave the other object broken. Nothing was dropped.

The DETAIL lists every dependent object, one per line, including ones further down the chain (a view built on a view that reads the table). The HINT offers CASCADE:

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

The same error appears for ALTER TABLE … DROP COLUMN, DROP TYPE, DROP FUNCTION and others. For roles it reads differently: role "x" cannot be dropped because some objects depend on it.

Common causes

1. A view (or materialised view) reads the table or column. The most common one, and views can be stacked: a view on a view on the table.
2. A foreign key in another table references this one.
3. A column uses the type (an enum or domain) you’re dropping.
4. A function is used by a column default, a generated column, a trigger or an index.
5. Dropping a role that still owns objects or holds privileges.

How to fix it

Read the list, then drop in order

Usually the right fix is to drop the dependants yourself, in order, so you know exactly what goes:

DROP VIEW customer_emails;
ALTER TABLE invoices DROP CONSTRAINT invoices_customer_id_fkey;
DROP TABLE customers;

Before dropping a view you want back, save its definition:

SELECT pg_get_viewdef('customer_emails'::regclass, true);

Use CASCADE, knowing what it does

CASCADE drops every dependent object, and everything that depends on those, in one go. It prints what it dropped as a NOTICE. Two things to know:

• For a foreign key, CASCADE drops the constraint, not the referencing table or its rows. invoices stays, without its foreign key.
• For a view, it drops the view, and any views built on it. Code and reports that use them stop working.

Run it in a transaction and read the NOTICE before you commit:

BEGIN;
DROP TABLE customers CASCADE;
-- read the NOTICE; ROLLBACK if it lists anything you didn't expect
COMMIT;

Find dependants yourself

For views that read a table directly (views built on those show up in the error’s DETAIL):

SELECT DISTINCT v.oid::regclass AS view
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class v ON v.oid = r.ev_class
WHERE d.refobjid = 'customers'::regclass
  AND v.oid <> 'customers'::regclass;

For foreign keys pointing at a table:

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

Changing a column used by a view

Changing a column’s type is blocked by views too, with a different message and code: cannot alter type of a column used by a view or rule. Save the view definitions, drop the views, change the column, and create the views again, all in one transaction. See changing a column’s type.

Dropping a role

A role that owns objects or has privileges can’t be dropped until they’re handed over or removed. In each database where it owns something, run:

REASSIGN OWNED BY old_role TO new_owner;
DROP OWNED BY old_role;

Then DROP ROLE old_role;. DROP OWNED also revokes the role’s privileges.

Reproduce it

On PostgreSQL 18.6:

CREATE TYPE invoice_status AS ENUM ('draft', 'sent', 'paid');
CREATE TABLE customers (id int PRIMARY KEY, name text, email text);
CREATE TABLE invoices (id int PRIMARY KEY, customer_id int REFERENCES customers, total numeric, status invoice_status);
CREATE VIEW customer_emails AS SELECT id, email FROM customers;

DROP TABLE customers;
ALTER TABLE customers DROP COLUMN email;
DROP TYPE invoice_status;

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

ERROR:  cannot drop column email of table customers because other objects depend on it
DETAIL:  view customer_emails depends on column email of table customers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

ERROR:  cannot drop type invoice_status because other objects depend on it
DETAIL:  column status of table invoices depends on type invoice_status
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

With CASCADE:

NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to constraint invoices_customer_id_fkey on table invoices
drop cascades to view customer_emails

Afterwards invoices still exists, with its rows, and no foreign key. With a second view built on customer_emails, the error and the NOTICE list it as well (view customer_emails2 depends on view customer_emails). A trigger function in use fails the same way: DETAIL: trigger t on table invoices depends on function trg(). A role that owns a table and has a grant on another:

ERROR:  role "seo_pgsql_owner" cannot be dropped because some objects depend on it
DETAIL:  privileges for table invoices
owner of table owned_thing

PostgreSQL 14 to 17 print the same messages. One difference: on 15 and later, dropping a function used by a generated column fails with cannot drop function add_tax(numeric) because other objects depend on it; on 14 the same DROP FUNCTION succeeds and silently drops the generated column with it.

In Inlet

On protected connections, Inlet asks you to type the table’s name before it runs a DROP. The ER diagram shows which tables reference which, and when a statement fails Inlet shows the error at the position the server reports. With your own Anthropic API key, Ask Claude (⌘L) can fix the failed statement, sending the schema, the SQL and the error, never rows.