ClickHouse DB::Exception: Cannot drop, has dependent objects

The "DB::Exception: Cannot drop, has dependent objects" error means you are trying to drop a table, view, or other object that is referenced by other objects in the system. ClickHouse raises the error code HAVE_DEPENDENT_OBJECTS to prevent you from accidentally breaking materialized views, dictionaries, or other constructs that depend on the object you are trying to remove.

Impact

The DROP statement is rejected, and the target object remains in place. This is a protective measure -- without it, dropping a source table would silently break all materialized views that read from it. The error forces you to acknowledge and handle dependencies explicitly.

Common Causes

1. Materialized views reading from the table -- the most common dependency. A materialized view has the table as its source in the SELECT ... FROM clause.
2. Dictionaries sourced from the table -- a dictionary uses the table as its data source.
3. Views (non-materialized) referencing the table -- standard views that include the table in their definition.
4. Cascading dependencies -- a materialized view depends on another materialized view, which depends on the table you want to drop.
5. Live views or window views -- specialized view types that maintain a dependency on their source table.

Troubleshooting and Resolution Steps

1. Identify the dependent objects. Check for views and materialized views:

   SELECT name, engine, create_table_query
   FROM system.tables
   WHERE create_table_query LIKE '%my_table%'
     AND name != 'my_table';
   

2. Check for dependent dictionaries:

   SELECT name, source
   FROM system.dictionaries
   WHERE source LIKE '%my_table%';
   

3. Drop dependent objects first, working from the outermost dependencies inward:

   -- Drop materialized views that depend on the table
   DROP TABLE IF EXISTS my_database.mv_dependent_on_my_table;
   
   -- Drop dictionaries that depend on the table
   DROP DICTIONARY IF EXISTS my_database.dict_from_my_table;
   
   -- Now drop the table
   DROP TABLE my_database.my_table;
   

4. If you want to see the full dependency chain, trace it manually. ClickHouse does not have a built-in dependency graph query, but you can search system.tables for references:

   SELECT database, name, engine
   FROM system.tables
   WHERE create_table_query LIKE '%FROM my_database.my_table%'
      OR create_table_query LIKE '%FROM my_database.`my_table`%';
   

5. If you are replacing the table, consider recreating the dependencies afterward:

   -- Save the view definitions
   SHOW CREATE TABLE my_database.mv_dependent_view;
   
   -- Drop in order
   DROP TABLE my_database.mv_dependent_view;
   DROP TABLE my_database.my_table;
   
   -- Recreate
   CREATE TABLE my_database.my_table (...) ENGINE = MergeTree() ORDER BY id;
   CREATE MATERIALIZED VIEW my_database.mv_dependent_view ...;
   

6. On a cluster, check dependencies across all nodes:

   SELECT hostName(), database, name
   FROM clusterAllReplicas('my_cluster', system.tables)
   WHERE create_table_query LIKE '%my_table%';
   

Best Practices

• Document the dependency graph of your ClickHouse tables, views, and dictionaries.
• When dropping tables in scripts, include the dependent objects in the correct order.
• Use naming conventions that make dependencies visible (e.g., mv_events_hourly for a materialized view on the events table).
• Before major schema changes, run a dependency check query to understand the blast radius.
• Consider using EXCHANGE TABLES to swap in a new version of a table without disrupting materialized views (if the schema remains compatible).

Frequently Asked Questions

Q: Does ClickHouse support CASCADE on DROP TABLE?
A: No. ClickHouse does not support CASCADE drops. You must manually drop dependent objects before dropping the parent object.

Q: Will renaming a table break its dependent views?
A: It depends. Materialized views that store data independently (with a TO clause) may continue to work. But views and materialized views defined with FROM my_table will break if my_table is renamed, since the dependency is by name.

Q: How do I find all materialized views in my system?
A: Query system.tables filtering on the engine: SELECT database, name FROM system.tables WHERE engine = 'MaterializedView';

Q: Can I temporarily detach a dependent view instead of dropping it?
A: Yes. DETACH TABLE my_view removes it from ClickHouse's catalog without deleting its definition. After you finish your operation, ATTACH TABLE my_view brings it back. However, the dependency check may still block the DROP even with detached views -- test this in your specific version.