DB::Exception: Cannot alter column 'col' because it is used in the primary key

Question

Attempting to Modify a Primary Key Column
When executing ALTER TABLE t MODIFY COLUMN col TYPE String on a MergeTree‑family table that already defines col in its primary key, ClickHouse throws the error DB::Exception: Cannot alter column 'col' because it is used in the primary key. The operation is rejected before any data mutation occurs.
Constraints and Uncertainty

Primary key columns determine the data layout of MergeTree parts; changing them would require rewriting all parts.
Current ClickHouse releases (up to 23.x) do not support in‑place primary key mutation.
Documentation lists this as a limitation, but no version has removed it.

The absence of a clear path to alter primary key columns raises a design question: should ClickHouse allow primary key changes through ALTER TABLE with automatic data reorganization, or is recreating the table the only viable approach?
Unresolved Questions

What would be the implications of adding support for primary key mutation in MergeTree tables, in terms of performance, consistency, and storage requirements?
Are there any experimental or beta releases that already implement a safe, incremental rewrite of parts when a primary key column is altered?
Could a hybrid approach—such as a two‑phase migration that first reindexes new parts and then drops old ones—be integrated into the existing ALTER TABLE framework?

Tasadduq Burney · Answer

Direct Answer
The error DB::Exception: Cannot alter column 'col' because it is used in the primary key is expected behavior. ClickHouse deliberately rejects ALTER TABLE ... MODIFY COLUMN on any column that participates in the primary (sorting) key of a MergeTree-family table. No stable release, including the 23.x line and later, supports in-place primary key mutation.

Why the Restriction Exists

Physical layout dependency: The primary key defines the sort order of every data part on disk. Changing the key would require rewriting all existing parts.
Resource impact: A full rewrite doubles temporary storage, generates heavy I/O, and blocks or complicates concurrent queries that expect a consistent layout.
Engine invariants: MergeTree's merge logic, TTL processing, and replication protocol all assume the sorting key is immutable for the lifetime of a part.

Standard Workaround: Recreate and Swap

Create a new table with the desired primary key and identical engine settings (including any ReplacingMergeTree version column or SummingMergeTree columns).
CREATE TABLE t_new (
  col String,
  ...
) ENGINE = MergeTree()
ORDER BY (col, ...);

Backfill data in manageable batches to avoid long-running transactions and excessive mutation queue growth.
INSERT INTO t_new SELECT * FROM t;

Verify row counts, part layout, and query performance on the new table.
SELECT count() FROM t;
SELECT count() FROM t_new;
SELECT * FROM system.parts WHERE table = 't_new' FORMAT Vertical;

Swap atomically using RENAME or EXCHANGE TABLES (the latter preserves UUIDs for replicated tables).
RENAME TABLE t TO t_old, t_new TO t;

Drop the old table after confirming the switch is successful.
DROP TABLE t_old;

Operational Considerations

Disk space: Ensure at least 2× the table size free during backfill.
Replicated tables: Apply the same DDL on all replicas; use EXCHANGE TABLES to keep UUIDs consistent.
Mutations: ALTER TABLE UPDATE/DELETE rewrites values but never changes the primary key definition, so they cannot bypass this restriction.

One Diagnostic Detail Needed
What is the exact output of SELECT version()? A small number of recent experimental builds have discussed incremental primary key rewrite, but none are in general availability. Knowing your precise version confirms whether any new alter capability exists for your deployment.

DB::Exception: Cannot alter column 'col' because it is used in the primary key

Attempting to Modify a Primary Key Column

Constraints and Uncertainty

Unresolved Questions

1 answer

Direct Answer

Why the Restriction Exists

Standard Workaround: Recreate and Swap

Operational Considerations

One Diagnostic Detail Needed

Your perspective belongs here.

0 question comments