PostgreSQL Dependent Objects Still Exist (SQLSTATE 2BP01)

When you run a DROP statement in PostgreSQL and the target object has dependencies, PostgreSQL raises ERROR: cannot drop <object> because other objects depend on it with SQLSTATE 2BP01 (dependent_objects_still_exist). The error message details which dependent objects are blocking the operation and suggests using CASCADE to remove them along with the target object.

What This Error Means

SQLSTATE 2BP01 belongs to PostgreSQL error class 2B — "E" (feature not supported in some contexts), but more accurately it is a DDL integrity protection error. PostgreSQL's dependency tracking system (pg_depend) records relationships between database objects. When you attempt to drop a table, column, type, schema, function, or other object, PostgreSQL checks this catalog before proceeding. If any dependent objects exist — views that reference a table, foreign keys pointing at a table, functions that use a type, columns of a type being dropped — the operation is blocked rather than leaving the database in an inconsistent state.

The transaction that issued the failing DROP is aborted at that statement. Any transaction block it was part of must be rolled back before issuing new commands. The database itself is unaffected; no objects are removed.

The error message always names the blocking objects explicitly, making it straightforward to understand what needs to be resolved before the drop can succeed:

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

Common Causes

1. Dropping a table referenced by a view. Views store their definition as a query against underlying tables. Dropping any table referenced in a view's definition triggers this error.

2. Dropping a table or column with foreign key references. If another table has a FOREIGN KEY constraint pointing to the table (or a specific column) being dropped, the constraint itself is a dependent object.

3. Dropping a type used by table columns or functions. If a custom composite type, enum, or domain is in use by one or more column definitions or function signatures, dropping it fails until all usages are removed.

4. Dropping a schema that contains objects. DROP SCHEMA myschema fails if the schema contains any tables, views, sequences, functions, or other objects.

5. Dropping a function or operator used by a function index or constraint. Functions referenced in expression indexes or CHECK constraints using custom operators create dependencies that block removal.

6. Dropping a column with a dependent view or generated column. Even dropping a single column from a table can trigger this error if any view selects that column by name or if another generated column references it.

How to Fix dependent_objects_still_exist

1. Use CASCADE to drop the object and all its dependents automatically. This is the quickest fix but removes all dependent objects. Review the DETAIL output carefully before using it in production.

   -- Before CASCADE: see exactly what will be removed
   -- Run the plain DROP first to read the DETAIL output, then cancel.
   DROP TABLE orders;
   -- ERROR: ... DETAIL: view order_summary depends on table orders
   
   -- Then apply CASCADE:
   DROP TABLE orders CASCADE;
   

2. Manually drop dependents first, then drop the target object. This gives you control over what is removed and lets you decide whether to recreate any of the dependent objects afterward.

   DROP VIEW order_summary;
   DROP TABLE orders;
   

3. Remove the foreign key constraint before dropping the referenced table or column.

   ALTER TABLE order_items DROP CONSTRAINT order_items_order_id_fkey;
   DROP TABLE orders;
   

4. Migrate columns to a new type before dropping the old type. If a custom type is in use, alter the columns to use a compatible built-in or replacement type first.

   ALTER TABLE products ALTER COLUMN status TYPE text USING status::text;
   DROP TYPE product_status;
   

5. Query pg_depend to enumerate all dependencies before making changes. This is especially useful when the dependency chain is deep or spans multiple schemas.

   SELECT
       dep.classid::regclass AS dep_class,
       dep.objid,
       dep.deptype,
       ref.classid::regclass AS ref_class,
       ref.objid AS ref_objid
   FROM pg_depend dep
   JOIN pg_depend ref ON dep.refobjid = ref.objid
   WHERE dep.refobjid = 'orders'::regclass;
   

   A simpler approach for tables is to use the \d+ tablename meta-command in psql, which shows indexes, constraints, and references.

Additional Information

• This error can appear in all actively supported PostgreSQL versions. The dependency tracking mechanism and the 2BP01 SQLSTATE have been stable for many years.
• Related SQLSTATE codes in DDL operations include 42P07 (duplicate_table) and 42P01 (undefined_table), which also arise during schema management.
• ORM migration tools (Django, Alembic, ActiveRecord, Flyway) that auto-generate DROP statements may hit this error when migration order is incorrect or when a view was created outside of the migration framework. The fix is typically to add an explicit migration step to drop the dependent view before dropping the table.
• DROP ... CASCADE is permanent and recursive — it will traverse the full dependency graph and drop everything downstream. In production databases, always run the plain DROP first to read the full DETAIL list before committing to CASCADE.
• PostgreSQL distinguishes between RESTRICT (the default, which raises 2BP01) and CASCADE. Explicitly writing DROP TABLE foo RESTRICT is equivalent to omitting the keyword.

Frequently Asked Questions

Why doesn't PostgreSQL just drop the dependent objects automatically? PostgreSQL defaults to RESTRICT behavior because silently removing objects that depend on the target would be surprising and potentially destructive. A view or foreign key may represent business logic that should not be deleted without explicit intent. The requirement to use CASCADE is a deliberate safety gate.

I used CASCADE and now a view I needed is gone. Can I recover it? If you have a backup, restore the view definition from there. If not, check pg_stat_activity or your query logs — if the DROP was recent, your shell history or application migration files may contain the original CREATE VIEW statement. Going forward, keep DDL under version control so view definitions are never lost.

Can I drop a column without dropping the views that reference it? Not directly in PostgreSQL without CASCADE. If you need to keep a view, you must recreate it without the dropped column. The typical pattern is: save the view definition, drop the view, drop the column, then recreate the view adjusted to exclude the removed column.

Does this error put my database in a bad state? No. The failing statement is rolled back and the database is left entirely intact. If the DROP was inside an explicit transaction block (BEGIN ... COMMIT), the entire transaction is aborted and you must issue a ROLLBACK before executing further statements. The objects you were trying to drop still exist.