ClickHouse DB::Exception: ALTER of column is forbidden

The "DB::Exception: ALTER of column is forbidden" error in ClickHouse is raised when you try to modify, rename, or drop a column that is part of the table's primary key, sorting key, partition key, or sampling expression. These columns are fundamental to how ClickHouse stores and retrieves data, and modifying them would invalidate the existing data layout. The error code is ALTER_OF_COLUMN_IS_FORBIDDEN.

Impact

The ALTER statement is rejected, and the column remains unchanged. This prevents accidental corruption of the table's key structure. While the error itself is harmless, it can block schema migration plans if the target column is part of a key expression.

Common Causes

1. Dropping a column that is part of the ORDER BY clause -- columns in the sorting key cannot be dropped because they define the physical data order.
2. Changing the type of a primary key column -- type changes on key columns are restricted to prevent data layout inconsistency.
3. Modifying a partition key column -- partition expressions determine how data is physically split into parts.
4. Altering a column used in a sampling expression -- the SAMPLE BY column cannot be dropped or changed.
5. Renaming a key column -- renaming columns that participate in key expressions is also forbidden.

Troubleshooting and Resolution Steps

1. Identify which key expressions the column participates in:

   SELECT
       name,
       sorting_key,
       primary_key,
       partition_key,
       sampling_key
   FROM system.tables
   WHERE database = 'default' AND name = 'your_table';
   

2. If you need to change a key column's type, create a new table and migrate data:

   -- Create new table with desired schema
   CREATE TABLE new_table AS old_table
   ENGINE = MergeTree()
   ORDER BY (new_key_expression);
   
   -- Migrate data
   INSERT INTO new_table SELECT * FROM old_table;
   
   -- Swap tables
   RENAME TABLE old_table TO old_table_backup, new_table TO old_table;
   

3. To remove a column from the sorting key, use ALTER TABLE MODIFY ORDER BY:

   -- Original: ORDER BY (a, b, c)
   -- Remove column 'c' from sorting key first
   ALTER TABLE your_table MODIFY ORDER BY (a, b);
   
   -- Now you can drop the column
   ALTER TABLE your_table DROP COLUMN c;
   

   Note: You can only remove trailing columns from the ORDER BY. The primary key must remain a prefix of the sorting key.

4. To change the partition key, create a new table with the desired partitioning:

   CREATE TABLE new_table (...)
   ENGINE = MergeTree()
   PARTITION BY toYYYYMM(new_date_column)
   ORDER BY (id, timestamp);
   

5. For replicated tables, ensure changes are applied through ON CLUSTER or on all replicas:

   ALTER TABLE your_table ON CLUSTER 'your_cluster' MODIFY ORDER BY (a, b);
   

Best Practices

• Plan your table's key structure carefully before creating it. Changing keys after data has been written requires data migration.
• Use MODIFY ORDER BY to adjust the sorting key when needed, keeping in mind the primary key prefix constraint.
• When designing schemas, minimize the number of columns in key expressions to retain flexibility for future schema changes.
• Document which columns are part of key expressions to prevent team members from attempting forbidden alterations.
• Test schema migrations in a staging environment first to catch forbidden ALTER errors before they block production deployments.

Frequently Asked Questions

Q: Can I change the type of a non-key column?
A: Yes. ALTER TABLE ... MODIFY COLUMN col_name NewType works for columns not in the primary key, sorting key, partition key, or sampling expression.

Q: Can I add a column to the sorting key?
A: Yes, you can append columns to the sorting key using ALTER TABLE ... MODIFY ORDER BY (existing_cols, new_col). The new column must already exist in the table. This is a metadata-only operation; existing parts retain their original order.

Q: Why can't I rename a key column?
A: Renaming a key column would require updating all parts' metadata and potentially the ZooKeeper path for replicated tables. ClickHouse does not support this operation due to the complexity and risk involved.

Q: Is there a way to force alter a key column?
A: No. There is no force option. The only path is to modify the key expression first (if allowed) or migrate data to a new table with the desired schema.