PostgreSQL 2BP01: dependent objects still exist
DROP refused because views, foreign keys, functions or other objects depend on the object. Drop or change those first, or use CASCADE with care.
- SQLSTATE
2BP01- Name
dependent_objects_still_exist
What it means
PostgreSQL tracks dependencies between objects. Dropping a table, column, type, function or role that something else uses fails with 2BP01 and lists the dependents in the DETAIL lines. DROP ... CASCADE would drop all of them too, which is rarely what you want on a shared database.
For roles the related message is role "app" cannot be dropped because some objects depend on it, which lists objects the role owns or has privileges on.
Common causes
- A view, materialized view or function uses the table or column.
- Another table has a foreign key that references this table.
- A column type is a custom type or enum that columns still use.
- A role still owns objects or holds privileges.
How to fix it
-
Read the
DETAILlines to see every dependent, then drop or change them deliberately.ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey; DROP TABLE customers;
-
List the views that depend on a table.
SELECT DISTINCT v.relname AS view_name 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;
-
To drop a role, hand its objects to another role and remove its privileges in every database it has them in.
REASSIGN OWNED BY old_role TO new_owner; DROP OWNED BY old_role; DROP ROLE old_role;
-
Use
CASCADEonly when you have read the list of dependents and want them all gone.DROP TABLE customers CASCADE;
Reproduce it safely
CREATE TEMP TABLE p (id int); CREATE TEMP VIEW pv AS SELECT * FROM p; DROP TABLE p; -- ERROR: cannot drop table p because other objects depend on it
Related errors
23503foreign key constraint violation55006object in use3726SQL Server: Can't drop: referenced by a FOREIGN KEY5074SQL Server: Object is dependent on columnORA-02449Oracle: unique/primary keys referenced by foreign keys1701MySQL: Can't truncate a table with foreign keys
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.