cockroachdb / cockroachdb/cockroach

sql/schemachanger: ALTER PRIMARY KEY does not support expression columns (CREATE TABLE does)

Open
#172,931 0 comments 0 reactions 0 assignees View on GitHub
A-schema-changes C-enhancement O-agent T-sql-foundations X-anchored-telemetry
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.