sql: ALTER TABLE ... ADD COLUMN ... PRIMARY KEY doesn't work correctly

[postamar]

To repro:

CREATE TABLE t (a INT PRIMARY KEY);
ALTER TABLE t ADD COLUMN b INT PRIMARY KEY;

Expected: the primary key is b an error is returned (in pg: multiple primary keys for table "t" are not allowed)
Actual: success, and a subsequent SHOW CREATE TABLE t yields

  table_name |              create_statement
-------------+---------------------------------------------
  t          | CREATE TABLE public.t (
             |     a INT8 NOT NULL,
             |     b INT8 NOT NULL,
             |     CONSTRAINT t_pkey PRIMARY KEY (a ASC),
             |     UNIQUE INDEX t_b_key (b ASC)
             | )
(1 row)

Jira issue: CRDB-15178

[otan]

seems more like we want an error for the second statement right?

e.g. PG

otan=# CREATE TABLE t (a INT PRIMARY KEY);
ALTER TABLE t ADD COLUMN b INT PRIMARY KEY;
CREATE TABLE
ERROR:  multiple primary keys for table "t" are not allowed

[postamar]

You're most likely correct, I didn't cross-check with postgres, sorry.

[ajwerner]

I agree we should reject CREATE TABLE t (a INT PRIMARY KEY); ALTER TABLE t ADD COLUMN b INT PRIMARY KEY;, but what to do in the case that the primary key is the implicit rowid primary key is less clear. Consider the following which is valid in postgres CREATE TABLE t (a INT); ALTER TABLE t ADD COLUMN b INT PRIMARY KEY;

[otan]

CREATE TABLE t (a INT PRIMARY KEY); ALTER TABLE t ADD COLUMN b INT PRIMARY KEY;

feels like we should definitely allow it if rowid is the hidden default PK... at least, it should be 100% true if it's the same txn - i think that is the case today

[email protected]:26257/defaultdb>  CREATE TABLE t (a INT PRIMARY KEY); ALTER TABLE t ADD COLUMN b INT PRIMARY KEY;
CREATE TABLE
ALTER TABLE

Note: timings for multiple statements on a single line are not supported. See https://go.crdb.dev/issue-v/48180/v22.2.

[email protected]:26257/defaultdb> show create table t;
  table_name |              create_statement
-------------+---------------------------------------------
  t          | CREATE TABLE public.t (
             |     a INT8 NOT NULL,
             |     b INT8 NOT NULL,
             |     CONSTRAINT t_pkey PRIMARY KEY (a ASC),
             |     UNIQUE INDEX t_b_key (b ASC)
             | )
(1 row)


Time: 25ms total (execution 24ms / network 1ms)

that said, it is totally broken in the new schema changer:

[email protected]:26257/defaultdb> CREATE TABLE t (a INT PRIMARY KEY);
CREATE TABLE


Time: 16ms total (execution 15ms / network 0ms)

[email protected]:26257/defaultdb> ALTER TABLE t ADD COLUMN b INT PRIMARY KEY;
ERROR: internal error: error executing PreCommitPhase stage 1 of 1 with 9 MutationType ops: relation "t" (104): duplicate constraint name: "crdb_internal_index_3_name_placeholder"
SQLSTATE: XX000
DETAIL: • Schema change plan for ALTER TABLE ‹defaultdb›.public.‹t› ADD COLUMN ‹b› INT8 PRIMARY KEY;
│
├── • PreCommitPhase