cockroachdb / cockroachdb/cockroach
sql/schemachanger: ALTER PRIMARY KEY does not support expression columns (CREATE TABLE does)
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Is your feature request related to a problem? Please describe.**
CockroachDB allows expression columns in a `PRIMARY KEY` at `CREATE TABLE` time, but rejects them via `ALTER PRIMARY KEY`. This asymmetry is enforced in code with an error message that reads like a permanent prohibition, when it's really an unimplemented capability.
This works:
```sql
CREATE TABLE t (a INT, b INT, c STRING, PRIMARY KEY ((a + b), c));
```
The expression is desugared into a hidden inaccessible virtual column (`crdb_internal_idx_expr`) that becomes a key column.
This does not:
```sql
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS ((a + b), c);
-- ERROR: expressions such as "a + b" are not allowed in primary index definition
```
And neither does pointing `ALTER PRIMARY KEY` at the already-materialized hidden column:
```sql
ALTER TABLE pk ALTER PRIMARY KEY USING COLUMNS (crdb_internal_idx_expr);
-- ERROR: cannot use inaccessible column "crdb_internal_idx_expr" in primary key
```
**Describe the solution you'd like**
Support expression columns in `ALTER PRIMARY KEY USING COLUMNS (...)`, desugaring them into the same hidden virtual column the `CREATE TABLE` path uses, then backfilling and swapping in the new primary index over existing data.
**Describe alternatives you've considered**
- Recreate the table with the desired expression PK at `CREATE TABLE` time and migrate data. Heavyweight and disruptive.
- Leave as-is; the current behavior is internally consistent (ALTER refuses both the raw expression and the hidden column), just asymmetric with CREATE.
**Additional context**
Note: PostgreSQL does not support expression primary keys at all, so CRDB's CREATE-TABLE support is already a superset of PG; this asymmetry is CRDB-specific and not a PG-compat concern.
Came up in #172739.
Code references:
- [alter_table_alter_primary_key.go:405-417](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/schemachanger/scbuild/internal/scbuildstmt/alter_table_alter_primary_key.go#L405-L417) (declarative schema changer)
- [alter_primary_key.go:128](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/alter_primary_key.go#L128) (legacy schema changer)
- Test evidence: `pkg/sql/logictest/testdata/logic_test/expression_index` (CREATE at ~L1514, ALTER rejection at ~L523)
Jira issue: CRDB-66219
Epic CRDB-60938
Contributor guide
Research direction
Start with pkg/sql/schemachanger/scbuild/internal/scbuildstmt/alter_table_alter_primary_key.go:405-417 and pkg/sql/alter_primary_key.go:128, then compare the CREATE path in pkg/sql/logictest/testdata/logic_test/expression_index. Run the expression_index logic test to confirm the existing CREATE success and ALTER rejection. Done means ALTER PRIMARY KEY accepts expression columns, uses the existing hidden virtual-column behavior, and successfully backfills and swaps the primary index over existing data.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100