alter_primary_key

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL, z INT NOT NULL, w INT, INDEX i (x), INDEX i2 (z))

statement ok
INSERT INTO t VALUES (1, 2, 3, 4), (5, 6, 7, 8)

query T noticetrace
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y, z)
----
NOTICE: primary key changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes

query IIII rowsort
SELECT * FROM t@t_pkey
----
1 2 3 4
5 6 7 8

statement ok
INSERT INTO t VALUES (9, 10, 11, 12)

query IIII rowsort
SELECT * from t@t_pkey
----
1 2 3 4
5 6 7 8
9 10 11 12

statement ok
UPDATE t SET x = 2 WHERE z = 7

query IIII rowsort
SELECT * from t@t_pkey
----
1 2 3 4
2 6 7 8
9 10 11 12

query T
SELECT feature_name FROM crdb_internal.feature_usage
WHERE feature_name IN ('sql.schema.alter_table.alter_primary_key') AND usage_count > 0
ORDER BY feature_name
----
sql.schema.alter_table.alter_primary_key

# Test primary key changes on storing indexes with different column families (the randomizer will do this for us).
statement ok
DROP TABLE t;

statement ok
CREATE TABLE t (
  x INT PRIMARY KEY, y INT, z INT NOT NULL, w INT, v INT,
  INDEX i1 (y) STORING (w, v), INDEX i2 (z) STORING (y, v)
);

statement ok
INSERT INTO t VALUES (1, 2, 3, 4, 5), (6, 7, 8, 9, 10), (11, 12, 13, 14, 15);

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (z);

statement ok
INSERT INTO t VALUES (16, 17, 18, 19, 20)

query III rowsort
SELECT y, w, v FROM t@i1
----
2 4 5
7 9 10
12 14 15
17 19 20

query III rowsort
SELECT y, z, v FROM t@i2
----
2 3 5
7 8 10
12 13 15
17 18 20

# Regression test for issue #81522.
statement error pgcode 42611 expressions such as \"gen_random_uuid\(\)\" are not allowed in primary index definition
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (gen_random_uuid());

# Test that composite values are encoded correctly in covering indexes.
statement ok
CREATE TABLE t_composite (x INT PRIMARY KEY, y DECIMAL NOT NULL);

statement ok
INSERT INTO t_composite VALUES (1, 1.0), (2, 1.001)

statement ok
ALTER TABLE t_composite ALTER PRIMARY KEY USING COLUMNS (y)

query IT rowsort
SELECT * FROM t_composite@t_composite_pkey
----
1 1.0
2 1.001

# Test that we can drop tables after a primary key change.
statement ok
DROP TABLE t_composite

subtest foreign_keys

# Test primary key changes on tables with inbound and outbound FK's.
statement ok
CREATE TABLE fk1 (x INT NOT NULL);

statement ok
CREATE TABLE fk2 (x INT NOT NULL, UNIQUE INDEX i (x));

statement ok
ALTER TABLE fk1 ADD CONSTRAINT fk FOREIGN KEY (x) REFERENCES fk2(x);

statement ok
INSERT INTO fk2 VALUES (1);

statement ok
INSERT INTO fk1 VALUES (1)

statement ok
ALTER TABLE fk1 ALTER PRIMARY KEY USING COLUMNS (x)

statement ok
INSERT INTO fk2 VALUES (2);

statement ok
INSERT INTO fk1 VALUES (2)

statement ok
ALTER TABLE fk2 ALTER PRIMARY KEY USING COLUMNS (x)

statement ok
INSERT INTO fk2 VALUES (3);
INSERT INTO fk1 VALUES (3)

# Test some self-referencing foreign keys.
statement ok
CREATE TABLE self (a INT PRIMARY KEY, x INT, y INT, z INT, w INT NOT NULL,
  INDEX (x), UNIQUE INDEX (y), INDEX (z));

statement ok
INSERT INTO self VALUES (1, 1, 1, 1, 1);

statement ok
ALTER TABLE self ADD CONSTRAINT fk1 FOREIGN KEY (z) REFERENCES self (y);

statement ok
ALTER TABLE self ADD CONSTRAINT fk2 FOREIGN KEY (x) REFERENCES self (y);

statement ok
ALTER TABLE self ALTER PRIMARY KEY USING COLUMNS (w)

statement ok
INSERT INTO self VALUES (2, 1, 2, 1, 2);
INSERT INTO self VALUES (3, 2, 3, 2, 3)

# Set up a bunch of foreign key references pointing into and out of a table.
statement ok
CREATE TABLE t1 (x INT PRIMARY KEY, y INT NOT NULL, z INT, w INT, INDEX (y), INDEX (z), UNIQUE INDEX (w));

statement ok
CREATE TABLE t2 (y INT, UNIQUE INDEX (y));

statement ok
CREATE TABLE t3 (z INT, UNIQUE INDEX (z));

statement ok
CREATE TABLE t4 (w INT, INDEX (w));

statement ok
CREATE TABLE t5 (x INT, INDEX (x));

statement ok
INSERT INTO t1 VALUES (1, 1, 1, 1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1);
INSERT INTO t4 VALUES (1);
INSERT INTO t5 VALUES (1);

statement ok
ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (y) REFERENCES t2(y);

statement ok
ALTER TABLE t1 ADD CONSTRAINT fk2 FOREIGN KEY (z) REFERENCES t3(z);

statement ok
ALTER TABLE t4 ADD CONSTRAINT fk3 FOREIGN KEY (w) REFERENCES t1(w);

statement ok
ALTER TABLE t5 ADD CONSTRAINT fk4 FOREIGN KEY (x) REFERENCES t1(x);

statement ok
ALTER TABLE t1 ALTER PRIMARY KEY USING COLUMNS (y)

statement ok
INSERT INTO t2 VALUES (5);
INSERT INTO t3 VALUES (6);
INSERT INTO t1 VALUES (7, 5, 6, 8);
INSERT INTO t4 VALUES (8);
INSERT INTO t5 VALUES (7)

statement error insert on table "t1" violates foreign key constraint "fk1"
INSERT INTO t1 VALUES (100, 100, 100, 100)

statement error insert on table "t4" violates foreign key constraint "fk3"
INSERT INTO t4 VALUES (101)

# Ensure that we still rewrite a primary index if the index column has name "rowid".
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (rowid INT PRIMARY KEY, y INT NOT NULL, FAMILY (rowid, y));

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   rowid INT8 NOT NULL,
   y INT8 NOT NULL,
   CONSTRAINT t_pkey PRIMARY KEY (y ASC),
   UNIQUE INDEX t_rowid_key (rowid ASC),
   FAMILY fam_0_rowid_y (rowid, y)
)

subtest index_rewrites
# Test that indexes that need to get rewritten indeed get rewritten.
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (
  x INT PRIMARY KEY,
  y INT NOT NULL, -- will be new primary key.
  z INT NOT NULL,
  w INT,
  v JSONB,
  INDEX i1 (w), -- will get rewritten.
  INDEX i2 (y), -- will get rewritten.
  UNIQUE INDEX i3 (z) STORING (y), -- will not be rewritten.
  UNIQUE INDEX i4 (z), -- will be rewritten.
  UNIQUE INDEX i5 (w) STORING (y), -- will be rewritten.
  INVERTED INDEX i6 (v), -- will be rewritten.
  INDEX i7 (z) USING HASH WITH (bucket_count=4), -- will be rewritten.
  FAMILY (x, y, z, w, v)
);

statement ok
INSERT INTO t VALUES (1, 2, 3, 4, '{}');

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   y INT8 NOT NULL,
   z INT8 NOT NULL,
   w INT8 NULL,
   v JSONB NULL,
   crdb_internal_z_shard_4 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(z)), 4:::INT8)) VIRTUAL,
   CONSTRAINT t_pkey PRIMARY KEY (y ASC),
   UNIQUE INDEX i3 (z ASC) STORING (y),
   UNIQUE INDEX t_x_key (x ASC),
   INDEX i1 (w ASC),
   INDEX i2 (y ASC),
   UNIQUE INDEX i4 (z ASC),
   UNIQUE INDEX i5 (w ASC) STORING (y),
   INVERTED INDEX i6 (v ASC),
   INDEX i7 (z ASC) USING HASH WITH (bucket_count=4),
   FAMILY fam_0_x_y_z_w_v (x, y, z, w, v)
)

# Test that the indexes we expect got rewritten. All but i3 should have been rewritten,
# so all but i3's indexID should be larger than 7.

query IT
SELECT index_id, index_name FROM crdb_internal.table_indexes WHERE descriptor_name = 't' ORDER BY index_id
----
4   i3
9   t_pkey
11  t_x_key
13  i1
15  i2
17  i4
19  i5
21  i6
23  i7

# Make sure that each index can index join against the new primary key;

query T
SELECT * FROM [EXPLAIN SELECT * FROM t@i1] OFFSET 2
----
·
• index join
│ table: t@t_pkey
│
└── • scan
      missing stats
      table: t@i1
      spans: FULL SCAN

query IIIIT
SELECT * FROM t@i1
----
1 2 3 4 {}

query T
SELECT * FROM [EXPLAIN SELECT * FROM t@i2] OFFSET 2
----
·
• index join
│ table: t@t_pkey
│
└── • scan
      missing stats
      table: t@i2
      spans: FULL SCAN

query IIIIT
SELECT * FROM t@i2
----
1 2 3 4 {}

query T
SELECT * FROM [EXPLAIN SELECT * FROM t@i3] OFFSET 2
----
·
• index join
│ table: t@t_pkey
│
└── • scan
      missing stats
      table: t@i3
      spans: FULL SCAN

query IIIIT
SELECT * FROM t@i3
----
1 2 3 4 {}

query T
SELECT * FROM [EXPLAIN SELECT * FROM t@i4] OFFSET 2
----
·
• index join
│ table: t@t_pkey
│
└── • scan
      missing stats
      table: t@i4
      spans: FULL SCAN

query IIIIT
SELECT * FROM t@i4
----
1 2 3 4 {}

query T
SELECT * FROM [EXPLAIN SELECT * FROM t@i5] OFFSET 2
----
·
• index join
│ table: t@t_pkey
│
└── • scan
      missing stats
      table: t@i5
      spans: FULL SCAN

query IIIIT
SELECT * FROM t@i5
----
1 2 3 4 {}

query T
SELECT * FROM [EXPLAIN SELECT * FROM t@i7] OFFSET 2
----
·
• index join
│ table: t@t_pkey
│
└── • scan
      missing stats
      table: t@i7
      spans: FULL SCAN

query IIIIT
SELECT * FROM t@i5
----
1 2 3 4 {}

subtest hash_sharded

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (
  x INT PRIMARY KEY,
  y INT NOT NULL,
  z INT,
  INDEX i1 (z) USING HASH WITH (bucket_count=5),
  FAMILY (x, y, z)
);

statement ok
INSERT INTO t VALUES (1, 2, 3);

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y) USING HASH WITH (bucket_count=10)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   y INT8 NOT NULL,
   z INT8 NULL,
   crdb_internal_z_shard_5 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(z)), 5:::INT8)) VIRTUAL,
   crdb_internal_y_shard_10 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(y)), 10:::INT8)) VIRTUAL,
   CONSTRAINT t_pkey PRIMARY KEY (y ASC) USING HASH WITH (bucket_count=10),
   UNIQUE INDEX t_x_key (x ASC),
   INDEX i1 (z ASC) USING HASH WITH (bucket_count=5),
   FAMILY fam_0_x_y_z (x, y, z)
)

query T
SELECT * FROM [EXPLAIN INSERT INTO t VALUES (4, 5, 6)] OFFSET 2
----
·
• insert fast path
  into: t(x, y, z, crdb_internal_z_shard_5, crdb_internal_y_shard_10)
  auto commit
  size: 7 columns, 1 row

# Ensure that all of the indexes have been rewritten.
query IT
SELECT index_id, index_name FROM crdb_internal.table_indexes WHERE descriptor_name = 't' ORDER BY index_id
----
3  t_pkey
5  t_x_key
7  i1

query III
SELECT * FROM t@t_pkey
----
1 2 3

query III
SELECT * FROM t@t_x_key
----
1 2 3

query III
SELECT * FROM t@i1
----
1 2 3


statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (
  x INT PRIMARY KEY USING HASH WITH (bucket_count=5),
  y INT NOT NULL,
  z INT,
  INDEX i (z),
  FAMILY (x, y, z)
);

statement ok
INSERT INTO t VALUES (1, 2, 3);

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   crdb_internal_x_shard_5 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(x)), 5:::INT8)) VIRTUAL,
   x INT8 NOT NULL,
   y INT8 NOT NULL,
   z INT8 NULL,
   CONSTRAINT t_pkey PRIMARY KEY (y ASC),
   UNIQUE INDEX t_x_key (x ASC) USING HASH WITH (bucket_count=5),
   INDEX i (z ASC),
   FAMILY fam_0_x_y_z (x, y, z)
)

query III
SELECT * FROM t@t_x_key
----
1 2 3

query III
SELECT * FROM t@i
----
1 2 3

# Ensure we don't rewrite default primary index even if its name isn't rowid.
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (rowid INT NOT NULL);

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   rowid INT8 NOT NULL,
   rowid_1 INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
   CONSTRAINT t_pkey PRIMARY KEY (rowid_1 ASC)
)

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (rowid)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   rowid INT8 NOT NULL,
   rowid_1 INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
   CONSTRAINT t_pkey PRIMARY KEY (rowid ASC)
)

# Regression for old primary key not using PrimaryIndexEncoding as its encoding type.
subtest encoding_bug

# This test ensures that while the old primary key is in the mutations list it is
# able to be updated and deleted with the primary index encoding.
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL, z INT NOT NULL, FAMILY (x, y, z));

statement ok
INSERT INTO t VALUES (1, 2, 3);

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (z);

statement ok
UPDATE t SET y = 3 WHERE z = 3

# Test for #45363.

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL)

statement ok
BEGIN

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y)

statement error pq: relation "t" \([0-9]+\): unimplemented: cannot perform other schema changes in the same transaction as a primary key change
CREATE INDEX ON t (y)

statement ok
ROLLBACK

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL)

statement ok
BEGIN

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y)

statement error pq: relation "t" \([0-9]+\): unimplemented: cannot perform other schema changes in the same transaction as a primary key change
ALTER TABLE t ADD COLUMN z INT

statement ok
ROLLBACK

subtest add_pk_rowid
# Tests for #45509.
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY)

statement error pq: multiple primary keys for table "t" are not allowed
ALTER TABLE t ADD PRIMARY KEY (x)

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT NOT NULL)

statement ok
ALTER TABLE t ADD PRIMARY KEY (x)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
   CONSTRAINT t_pkey PRIMARY KEY (x ASC)
)

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT NOT NULL);

statement ok
ALTER TABLE t ADD PRIMARY KEY (x) USING HASH WITH (bucket_count=4)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
   crdb_internal_x_shard_4 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(x)), 4:::INT8)) VIRTUAL,
   CONSTRAINT t_pkey PRIMARY KEY (x ASC) USING HASH WITH (bucket_count=4)
)

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT NOT NULL);

statement ok
ALTER TABLE t ADD CONSTRAINT "my_pk" PRIMARY KEY (x)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
   CONSTRAINT my_pk PRIMARY KEY (x ASC)
)

statement ok
CREATE INDEX i ON t (x);

statement error pgcode 42710 constraint with name \"i\" already exists
ALTER TABLE t DROP CONSTRAINT "my_pk", ADD CONSTRAINT "i" PRIMARY KEY (x);

# Regression for #45362.
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT NOT NULL)

statement ok
BEGIN

statement ok
ALTER TABLE t ADD COLUMN y INT

statement error pq: unimplemented: cannot perform a primary key change on t with other schema changes on t in the same transaction
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (x)

statement ok
ROLLBACK

# Ensure that starting a primary key change that does not
# enqueue any mutations doesn't start a job.
# TODO (rohany): This test might become obselete when #44923 is fixed.
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT NOT NULL);

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (x)

query I
SELECT job_id FROM [SHOW JOBS] WHERE
description = 'CLEANUP JOB for ''ALTER TABLE test.public.t ALTER PRIMARY KEY USING COLUMNS (y)''' AND
status = 'running'
----

subtest add_drop_pk

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL, FAMILY (x), FAMILY (y))

statement error pq: relation "t" \([0-9]+\): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
ALTER TABLE t DROP CONSTRAINT "t_pkey"

statement error pq: multiple primary keys for table "t" are not allowed
ALTER TABLE t ADD CONSTRAINT "t_pkey" PRIMARY KEY (y), DROP CONSTRAINT "t_pkey"

statement error pq: multiple primary keys for table "t" are not allowed
ALTER TABLE t ADD CONSTRAINT "t_pkey" PRIMARY KEY (y)

statement ok
ALTER TABLE t DROP CONSTRAINT "t_pkey", ADD CONSTRAINT "t_pkey" PRIMARY KEY (y)

statement ok
ALTER TABLE t DROP CONSTRAINT "t_pkey", ADD CONSTRAINT "t_pkey_v2" PRIMARY KEY (y)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   y INT8 NOT NULL,
   CONSTRAINT t_pkey_v2 PRIMARY KEY (y ASC),
   FAMILY fam_0_x (x),
   FAMILY fam_1_y (y)
)

statement ok
ALTER TABLE t ADD CONSTRAINT IF NOT EXISTS "t_pkey" PRIMARY KEY (x)

# Test that we can issue a DROP CONSTRAINT + ADD PRIMARY KEY
# in the same transaction.
statement ok
DROP TABLE t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL, FAMILY (x), FAMILY (y))

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT "t_pkey"

statement ok
ALTER TABLE t ADD CONSTRAINT "t_pkey" PRIMARY KEY (y)

statement ok
ROLLBACK;

statement ok
DROP TABLE t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL, FAMILY (x), FAMILY (y))

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT "t_pkey"

statement ok
ALTER TABLE t ADD CONSTRAINT "t_pkey_v2" PRIMARY KEY (y)

statement ok
COMMIT

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   y INT8 NOT NULL,
   CONSTRAINT t_pkey_v2 PRIMARY KEY (y ASC),
   FAMILY fam_0_x (x),
   FAMILY fam_1_y (y)
)

# Ensure that we can't use a table with a dropped primary key
# in any DML statements.
statement ok
DROP TABLE t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL)

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT t_pkey

statement error pgcode 55C02 requested table does not have a primary key
INSERT INTO t VALUES (1, 1)

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT t_pkey

statement error pgcode 55C02 pq: requested table does not have a primary key
DELETE FROM t WHERE x = 1

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT t_pkey

statement error pgcode 55C02 pq: requested table does not have a primary key
UPDATE t SET x = 1 WHERE y = 1

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT t_pkey

statement error pgcode 55C02 pq: requested table does not have a primary key
SELECT * FROM t

statement ok
ROLLBACK

# Ensure that DDL statements that don't add a primary key
# as their first operation don't succeed either.

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT t_pkey

statement error pgcode 55C02 pq: requested table does not have a primary key
CREATE INDEX ON t(x)

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT t_pkey

statement error pgcode 55C02 pq: requested table does not have a primary key
ALTER TABLE t ADD COLUMN z INT

statement ok
ROLLBACK

statement ok
BEGIN

statement ok
ALTER TABLE t DROP CONSTRAINT t_pkey

statement error pgcode 55C02 pq: requested table does not have a primary key
ALTER TABLE t ADD COLUMN z INT, ADD PRIMARY KEY (x)

statement ok
ROLLBACK

# Ensure that other changes in the same transaction
# as a DROP PRIMARY KEY get rolled back on failure.
statement ok
DROP TABLE IF EXISTS t1, t2 CASCADE;

statement ok
CREATE TABLE t1 (x INT PRIMARY KEY, y INT NOT NULL);

statement ok
CREATE TABLE t2 (x INT)

statement ok
BEGIN

statement ok
ALTER TABLE t1 DROP CONSTRAINT t1_pkey

statement ok
INSERT INTO t2 VALUES (1)

statement error pq: relation "t1" \([0-9]+\): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
COMMIT

query I
SELECT * FROM t2
----

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY, y INT NOT NULL)

statement error pq: table "t" does not have a primary key, cannot perform ADD COLUMN z INT8 AS \(x \+ 1\) STORED
ALTER TABLE t DROP CONSTRAINT t_pkey, ADD COLUMN z INT AS (x + 1) STORED, ADD PRIMARY KEY (y)


subtest create_table_change_pk

statement ok
DROP TABLE IF EXISTS t CASCADE

statement ok
BEGIN

statement ok
CREATE TABLE t (x INT NOT NULL, y INT, FAMILY (x, y), INDEX (y))

statement ok
ALTER TABLE t ADD PRIMARY KEY (x)

statement ok
COMMIT

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   y INT8 NULL,
   rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
   CONSTRAINT t_pkey PRIMARY KEY (x ASC),
   INDEX t_y_idx (y ASC),
   FAMILY fam_0_x_y_rowid (x, y, rowid)
)

# Ensure that index y got rewritten. If it was not rewritten,
# it would have an id less than 3.
query IT
SELECT index_id, index_name FROM crdb_internal.table_indexes WHERE descriptor_name = 't' ORDER BY index_id
----
3  t_pkey
5  t_y_idx

# Repeat the above test using ALTER PRIMARY KEY.

statement ok
DROP TABLE IF EXISTS t

statement ok
BEGIN

statement ok
CREATE TABLE t (x INT NOT NULL, y INT, FAMILY (x, y), INDEX (y))

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (x)

statement ok
COMMIT

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   y INT8 NULL,
   rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
   CONSTRAINT t_pkey PRIMARY KEY (x ASC),
   INDEX t_y_idx (y ASC),
   FAMILY fam_0_x_y_rowid (x, y, rowid)
)

# Ensure that index y got rewritten. If it was not rewritten,
# it would have an id less than 3.
query IT
SELECT index_id, index_name FROM crdb_internal.table_indexes WHERE descriptor_name = 't' ORDER BY index_id
----
3  t_pkey
5  t_y_idx

# Test when multiple indexes get created and destroyed.
statement ok
DROP TABLE IF EXISTS t

statement ok
BEGIN

statement ok
CREATE TABLE t (
  x INT NOT NULL, y INT, z INT, w INT,
  INDEX i1 (y), UNIQUE INDEX i2 (z),
  INDEX i3 (w) STORING (y, z),
  FAMILY (x, y, z, w)
)

statement ok
ALTER TABLE t ADD PRIMARY KEY (x)

statement ok
COMMIT

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   x INT8 NOT NULL,
   y INT8 NULL,
   z INT8 NULL,
   w INT8 NULL,
   rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
   CONSTRAINT t_pkey PRIMARY KEY (x ASC),
   INDEX i1 (y ASC),
   UNIQUE INDEX i2 (z ASC),
   INDEX i3 (w ASC) STORING (y, z),
   FAMILY fam_0_x_y_z_w_rowid (x, y, z, w, rowid)
)

# All index id's should be larger than 4.
query IT
SELECT index_id, index_name FROM crdb_internal.table_indexes WHERE descriptor_name = 't' ORDER BY index_id
----
5  t_pkey
7  i1
9  i2
11 i3

# Regression for #45889.
# Primary key changes on a hash sharded index that just change the bucket
# count shouldn't cause the old primary key to be copied.
statement ok
DROP TABLE IF EXISTS t CASCADE;

statement ok
CREATE TABLE t (x INT PRIMARY KEY USING HASH WITH (bucket_count=2));

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (x) USING HASH WITH (bucket_count=3)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   crdb_internal_x_shard_2 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(x)), 2:::INT8)) VIRTUAL,
   x INT8 NOT NULL,
   crdb_internal_x_shard_3 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(x)), 3:::INT8)) VIRTUAL,
   CONSTRAINT t_pkey PRIMARY KEY (x ASC) USING HASH WITH (bucket_count=3)
)

# Changes on a hash sharded index that change the columns will cause the old
# primary key to be copied.
statement ok
DROP TABLE t;

statement ok
CREATE TABLE t (x INT PRIMARY KEY USING HASH WITH (bucket_count=2), y INT NOT NULL, FAMILY (x, y));

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y) USING HASH WITH (bucket_count=2)

query TT
SHOW CREATE t
----
t  CREATE TABLE public.t (
   crdb_internal_x_shard_2 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(x)), 2:::INT8)) VIRTUAL,
   x INT8 NOT NULL,
   y INT8 NOT NULL,
   crdb_internal_y_shard_2 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(y)), 2:::INT8)) VIRTUAL,
   CONSTRAINT t_pkey PRIMARY KEY (y ASC) USING HASH WITH (bucket_count=2),
   UNIQUE INDEX t_x_key (x ASC) USING HASH WITH (bucket_count=2),
   FAMILY fam_0_x_y (x, y)
)

# Regression for #49079.
statement ok
DROP TABLE t;

statement ok
CREATE TABLE t (x INT, y INT, z INT, PRIMARY KEY (x, y));

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y);

statement ok
SET sql_safe_updates=false;

statement ok
ALTER TABLE t DROP COLUMN z

# Regression for #54629.
statement ok
CREATE TABLE t54629 (c INT NOT NULL, UNIQUE INDEX (c));

statement ok
ALTER TABLE t54629 ALTER PRIMARY KEY USING COLUMNS (c);

statement ok
INSERT INTO t54629 VALUES (1);

statement ok
DELETE FROM t54629 WHERE c = 1

statement ok
DROP TABLE t54629;

statement ok
CREATE TABLE t54629(a INT PRIMARY KEY, c INT NOT NULL, UNIQUE INDEX (c));

statement ok
ALTER TABLE t54629 ALTER PRIMARY KEY USING COLUMNS (c);

statement ok
DROP INDEX t54629_a_key CASCADE;

statement ok
INSERT INTO t54629 VALUES (1, 1);

statement ok
DELETE FROM t54629 WHERE c = 1;

# Validate ALTER ADD PRIMARY KEY idempotence for #59307
statement ok
DROP TABLE t1 CASCADE;

statement ok
create table t1(id integer not null, id2 integer not null, name varchar(32));

query TTT
select index_name,column_name,direction from [show indexes from t1] where index_name like 'primary%';
----

statement ok
alter table t1 alter primary key using columns(id, id2);


query TTT
select index_name,column_name,direction from [show indexes from t1];
----
t1_pkey  id     ASC
t1_pkey  id2    ASC
t1_pkey  name   N/A
t1_pkey  rowid  N/A


statement ok
alter table t1 alter primary key using columns(id, id2);


query TTT
select index_name,column_name,direction from [show indexes from t1];
----
t1_pkey  id     ASC
t1_pkey  id2    ASC
t1_pkey  name   N/A
t1_pkey  rowid  N/A

# Validate drop and recreate
statement ok
alter table t1 drop constraint t1_pkey, alter primary key using columns(id, id2);

query TTT
select index_name,column_name,direction from [show indexes from t1];
----
t1_pkey  id     ASC
t1_pkey  id2    ASC
t1_pkey  name   N/A
t1_pkey  rowid  N/A

statement ok
alter table t1 alter primary key using columns(id);


query TTT
select index_name,column_name,direction from [show indexes from t1];
----
t1_pkey        id     ASC
t1_pkey        id2    N/A
t1_pkey        name   N/A
t1_pkey        rowid  N/A
t1_id_id2_key  id     ASC
t1_id_id2_key  id2    ASC

statement ok
alter table t1 alter primary key using columns(id desc);


query TTT
select index_name,column_name,direction from [show indexes from t1];
----
t1_pkey        id     DESC
t1_pkey        id2    N/A
t1_pkey        name   N/A
t1_pkey        rowid  N/A
t1_id_key      id     ASC
t1_id_id2_key  id     ASC
t1_id_id2_key  id2    ASC


statement ok
alter table t1 alter primary key using columns(id desc);

query TTT
select index_name,column_name,direction from [show indexes from t1];
----
t1_pkey        id     DESC
t1_pkey        id2    N/A
t1_pkey        name   N/A
t1_pkey        rowid  N/A
t1_id_key      id     ASC
t1_id_id2_key  id     ASC
t1_id_id2_key  id2    ASC

statement ok
alter table t1 alter primary key using columns(id desc);

query TTT
select index_name,column_name,direction from [show indexes from t1];
----
t1_pkey        id     DESC
t1_pkey        id2    N/A
t1_pkey        name   N/A
t1_pkey        rowid  N/A
t1_id_key      id     ASC
t1_id_id2_key  id     ASC
t1_id_id2_key  id2    ASC

statement ok
alter table t1 alter primary key using columns(id) USING HASH WITH (bucket_count=10)

query TTT
select index_name,column_name,direction from [show indexes from t1];
----
t1_pkey        crdb_internal_id_shard_10  ASC
t1_pkey        id                         ASC
t1_pkey        id2                        N/A
t1_pkey        name                       N/A
t1_pkey        rowid                      N/A
t1_id_key1     id                         DESC
t1_id_key1     crdb_internal_id_shard_10  ASC
t1_id_key      id                         ASC
t1_id_key      crdb_internal_id_shard_10  ASC
t1_id_id2_key  id                         ASC
t1_id_id2_key  id2                        ASC
t1_id_id2_key  crdb_internal_id_shard_10  ASC

statement ok
CREATE TABLE table_with_virtual_cols (
  id INT PRIMARY KEY,
  new_pk INT NOT NULL,
  virtual_col INT AS (1::int) VIRTUAL,
  FAMILY (id, new_pk)
);
ALTER TABLE table_with_virtual_cols ALTER PRIMARY KEY USING COLUMNS (new_pk)

query TT
SHOW CREATE TABLE table_with_virtual_cols
----
table_with_virtual_cols  CREATE TABLE public.table_with_virtual_cols (
                         id INT8 NOT NULL,
                         new_pk INT8 NOT NULL,
                         virtual_col INT8 NULL AS (1:::INT8) VIRTUAL,
                         CONSTRAINT table_with_virtual_cols_pkey PRIMARY KEY (new_pk ASC),
                         UNIQUE INDEX table_with_virtual_cols_id_key (id ASC),
                         FAMILY fam_0_id_new_pk (id, new_pk)
)

# Test that we do not create new indexes for the old primary key when going
# from sharded to non-sharded and back.
subtest toggle_sharded_no_new_index

statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (i INT PRIMARY KEY)

query TTT
SELECT index_name,column_name,direction FROM [SHOW INDEXES FROM t]
----
t_pkey  i  ASC

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (i) USING HASH WITH (bucket_count=2)

query TTT
SELECT index_name,column_name,direction FROM [SHOW INDEXES FROM t]
----
t_pkey  crdb_internal_i_shard_2  ASC
t_pkey  i                        ASC

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (i);

query TTT
SELECT index_name,column_name,direction FROM [SHOW INDEXES FROM t]
----
t_pkey  i                        ASC

# Regression tests for incorrectly reading from the unique secondary index that
# used be a primary index in the vectorized engine (#71553). Note that this
# reproduction only works on 21.1 and before (because it relies on a bug with
# incorrectly marking the secondary index as having a primary index encoding
# #71552).
statement ok
CREATE TABLE t71553 (a INT PRIMARY KEY, b INT NOT NULL);

statement ok
INSERT INTO t71553 VALUES (1, 1);

statement ok
ALTER TABLE t71553 ALTER PRIMARY KEY USING COLUMNS (b);

query II
SELECT * FROM t71553
----
1  1

statement ok
ALTER TABLE t71553 ALTER PRIMARY KEY USING COLUMNS (a);

query II
SELECT * FROM t71553
----
1  1

query II
SELECT * FROM t71553@t71553_a_key
----
1  1

query II
SELECT * FROM t71553@t71553_b_key
----
1  1

subtest virtual_primary_index
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (
  a INT NOT NULL,
  b INT NOT NULL,
  k INT NOT NULL AS (a+b) VIRTUAL,
  PRIMARY KEY (a),
  INDEX t_idx_b_k (b, k),
  FAMILY "primary" (a, b)
);

statement ok
INSERT INTO t VALUES (1,2), (3,4);

query III colnames,rowsort
SELECT * FROM t@t_pkey;
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_idx_b_k;
----
a  b  k
1  2  3
3  4  7

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (k);

query T
SELECT @2 FROM [SHOW CREATE TABLE t];
----
CREATE TABLE public.t (
   a INT8 NOT NULL,
   b INT8 NOT NULL,
   k INT8 NOT NULL AS (a + b) VIRTUAL,
   CONSTRAINT t_pkey PRIMARY KEY (k ASC),
   UNIQUE INDEX t_a_key (a ASC),
   INDEX t_idx_b_k (b ASC, k ASC)
)

query III colnames,rowsort
SELECT * FROM t@t_pkey
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_a_key
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_idx_b_k
----
a  b  k
1  2  3
3  4  7

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (b, k);

query T
SELECT @2 FROM [SHOW CREATE TABLE t];
----
CREATE TABLE public.t (
   a INT8 NOT NULL,
   b INT8 NOT NULL,
   k INT8 NOT NULL AS (a + b) VIRTUAL,
   CONSTRAINT t_pkey PRIMARY KEY (b ASC, k ASC),
   UNIQUE INDEX t_k_key (k ASC),
   UNIQUE INDEX t_a_key (a ASC),
   INDEX t_idx_b_k (b ASC, k ASC)
)

query III colnames,rowsort
SELECT * FROM t@t_pkey
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_a_key
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_k_key
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_idx_b_k
----
a  b  k
1  2  3
3  4  7

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (a);

query T
SELECT @2 FROM [SHOW CREATE TABLE t];
----
CREATE TABLE public.t (
   a INT8 NOT NULL,
   b INT8 NOT NULL,
   k INT8 NOT NULL AS (a + b) VIRTUAL,
   CONSTRAINT t_pkey PRIMARY KEY (a ASC),
   UNIQUE INDEX t_b_k_key (b ASC, k ASC),
   UNIQUE INDEX t_k_key (k ASC),
   UNIQUE INDEX t_a_key (a ASC),
   INDEX t_idx_b_k (b ASC, k ASC)
)

query III colnames,rowsort
SELECT * FROM t@t_pkey
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_a_key
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_k_key
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_idx_b_k
----
a  b  k
1  2  3
3  4  7

query III colnames,rowsort
SELECT * FROM t@t_b_k_key
----
a  b  k
1  2  3
3  4  7

subtest test_storage_params_validation

statement ok
CREATE TABLE t_test_param (
  a INT PRIMARY KEY,
  b INT NOT NULL,
  FAMILY fam_0_a_b (a, b)
);

statement error pq: invalid storage param "s2_max_level" on primary key
ALTER TABLE t_test_param ALTER PRIMARY KEY USING COLUMNS (b) WITH (s2_max_level=20);

statement error pq: invalid storage param "s2_max_level" on primary key
ALTER TABLE t_test_param ALTER PRIMARY KEY USING COLUMNS (b) USING HASH WITH (s2_max_level=20);

statement error pq: "bucket_count" storage param should only be set with "USING HASH" for hash sharded index
ALTER TABLE t_test_param ALTER PRIMARY KEY USING COLUMNS (b) WITH (bucket_count=5);

statement error pq: "bucket_count" storage parameter and "BUCKET_COUNT" cannot be set at the same time
ALTER TABLE t_test_param ALTER PRIMARY KEY USING COLUMNS (b) USING HASH WITH BUCKET_COUNT = 5 WITH (bucket_count=5);

# Make sure old BUCKET_COUNT syntax still works
statement ok
ALTER TABLE t_test_param ALTER PRIMARY KEY USING COLUMNS (b) USING HASH WITH BUCKET_COUNT = 5;

query T
SELECT @2 FROM [SHOW CREATE TABLE t_test_param]
----
CREATE TABLE public.t_test_param (
   a INT8 NOT NULL,
   b INT8 NOT NULL,
   crdb_internal_b_shard_5 INT8 NOT VISIBLE NOT NULL AS (mod(fnv32(crdb_internal.datums_to_bytes(b)), 5:::INT8)) VIRTUAL,
   CONSTRAINT t_test_param_pkey PRIMARY KEY (b ASC) USING HASH WITH (bucket_count=5),
   UNIQUE INDEX t_test_param_a_key (a ASC),
   FAMILY fam_0_a_b (a, b)
)

subtest pkey-comment-drop

# Create a table with a primary key and add a comment on it.
statement ok
CREATE TABLE pkey_comment (a INT8, b INT8, c INT8, CONSTRAINT pkey PRIMARY KEY (a, b));

statement ok
COMMENT ON INDEX pkey IS 'idx';
COMMENT ON CONSTRAINT pkey ON pkey_comment IS 'const';

statement ok
CREATE UNIQUE INDEX i2 ON pkey_comment(c);

statement ok
COMMENT ON INDEX i2 IS 'idx2';
COMMENT ON CONSTRAINT i2 ON pkey_comment IS 'idx3';


# Comment should exist inside the create statement, so filter it out
query T
SELECT substring(@2, strpos(@2, 'COMMENT')) FROM [SHOW CREATE pkey_comment];
----
COMMENT ON INDEX public.pkey_comment@pkey IS 'idx';
COMMENT ON INDEX public.pkey_comment@i2 IS 'idx2';
COMMENT ON CONSTRAINT pkey ON public.pkey_comment IS 'const';
COMMENT ON CONSTRAINT i2 ON public.pkey_comment IS 'idx3'


# Altering the primary key should clean up the comment.
statement ok
ALTER TABLE pkey_comment ALTER PRIMARY KEY USING COLUMNS (b);


# No comment exists inside the CREATE statement
query T
SELECT substring(@2, strpos(@2, 'COMMENT')) FROM [SHOW CREATE pkey_comment];
----
COMMENT ON INDEX public.pkey_comment@i2 IS 'idx2';
COMMENT ON CONSTRAINT pkey_comment_a_b_key ON public.pkey_comment IS 'const';
COMMENT ON CONSTRAINT i2 ON public.pkey_comment IS 'idx3'

subtest test-index-deduplication

# Regression for #78046: `ALTER PRIMARY KEY` should not create a secondary
# index on the (old) primary key columns when there is already a secondary
# index thereof.
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (a INT PRIMARY KEY, b INT NOT NULL, UNIQUE INDEX t_a_key (a));

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (b);

query TTT
SELECT index_name, column_name, direction FROM [SHOW INDEXES FROM t]
----
t_pkey   b  ASC
t_pkey   a  N/A
t_a_key  a  ASC
t_a_key  b  ASC

# But if the existing index is not strictly equal to the (old) primary key
# (even if, for example, the (old) primary key is a strict prefix of an
# existing index), a secondary index on the (old) PK columns will still be
# created.
statement ok
DROP TABLE IF EXISTS t;

statement ok
CREATE TABLE t (a INT PRIMARY KEY, b INT NOT NULL, UNIQUE INDEX t_a_b_key (a, b));

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (b);

# Note that index `t_a_b_key` and `t_a_key` shall not be deemed as identical
# even if they both have columns (a, b) in the sense that one enforces uniqueness
# on only (a) but the other on (a, b).
query TTTB
SELECT index_name, column_name, direction, storing FROM [SHOW INDEXES FROM t]
----
t_pkey     b  ASC  false
t_pkey     a  N/A  true
t_a_b_key  a  ASC  false
t_a_b_key  b  ASC  false
t_a_key    a  ASC  false
t_a_key    b  ASC  true

# The following regression test makes sure that when the new PK columns
# is a (strict) subset of the old PK columns, all existing secondary indexes
# were rewritten, and hence dropping a column from the old PK columns does not
# unexpectedly drop an existing secondary index.
subtest regression_#84040

statement ok
DROP TABLE IF EXISTS t

statement ok
CREATE TABLE t (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  PRIMARY KEY (a, b),
  UNIQUE INDEX uniq_idx (c)
);

statement ok
ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (a)

statement ok
ALTER TABLE t DROP COLUMN b

query TTT
select index_name,column_name,direction from [show indexes from t];
----
t_pkey      a            ASC
t_pkey      c            N/A
uniq_idx    c            ASC
uniq_idx    a            ASC