The ALTER PRIMARY KEY statement is a subcommand of ALTER TABLE that can be used to change the primary key of a table.
The ALTER PRIMARY KEY statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Watch the demo
Details
You cannot change the primary key of a table that is currently undergoing a primary key change, or any other schema change.
ALTER PRIMARY KEYmight need to rewrite multiple indexes, which can make it an expensive operation.When you change a primary key with
ALTER PRIMARY KEY, the old primary key index becomes aUNIQUEsecondary index. This helps optimize the performance of queries that still filter on the old primary key column.ALTER PRIMARY KEYdoes not alter the partitions on a table or its indexes, even if a partition is defined on a column in the original primary key. If you alter the primary key of a partitioned table, you must update the table partition accordingly.The secondary index created by
ALTER PRIMARY KEYwill not be partitioned, even if a partition is defined on a column in the original primary key. To ensure that the table is partitioned correctly, you must create a partition on the secondary index, or drop the secondary index.Any new primary key column set by
ALTER PRIMARY KEYmust have an existingNOT NULLconstraint. To add aNOT NULLconstraint to an existing column, useALTER TABLE ... ALTER COLUMN ... SET NOT NULL.
To change an existing primary key without creating a secondary index from that primary key, use DROP CONSTRAINT ... PRIMARY KEY/ADD CONSTRAINT ... PRIMARY KEY. For examples, see the ADD CONSTRAINT and DROP CONSTRAINT pages.
Synopsis
Parameters
| Parameter | Description |
|---|---|
table_name |
The name of the table with the primary key that you want to modify. |
index_params |
The name of the column(s) that you want to use for the primary key. These columns replace the current primary key column(s). |
USING HASH |
Creates a hash-sharded index. |
Required privileges
The user must have the CREATE privilege on a table to alter its primary key.
Viewing schema changes
This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.
Examples
Alter a single-column primary key
Suppose that you are storing the data for users of your application in a table called users, defined by the following CREATE TABLE statement:
> CREATE TABLE users (
name STRING PRIMARY KEY,
email STRING
);
The primary key of this table is on the name column. This is a poor choice, as some users likely have the same name, and all primary keys enforce a UNIQUE constraint on row values of the primary key column. Per our best practices, you should instead use a UUID for single-column primary keys, and populate the rows of the table with generated, unique values.
You can add a column and change the primary key with a couple of ALTER TABLE statements:
> ALTER TABLE users ADD COLUMN id UUID NOT NULL DEFAULT gen_random_uuid();
> ALTER TABLE users ALTER PRIMARY KEY USING COLUMNS (id);
> SHOW CREATE TABLE users;
table_name | create_statement
-------------+--------------------------------------------------
users | CREATE TABLE users (
| name STRING NOT NULL,
| email STRING NULL,
| id UUID NOT NULL DEFAULT gen_random_uuid(),
| CONSTRAINT users_pkey PRIMARY KEY (id ASC),
| UNIQUE INDEX users_name_key (name ASC)
| )
(1 row)
Alter an existing primary key to use hash sharding
Let's assume the events table already exists:
> CREATE TABLE events (
product_id INT8,
owner UUID,
serial_number VARCHAR,
event_id UUID,
ts TIMESTAMP,
data JSONB,
PRIMARY KEY (product_id, owner, serial_number, ts, event_id),
INDEX (ts) USING HASH
);
You can change an existing primary key to use hash sharding by adding the USING HASH clause at the end of the key definition:
> ALTER TABLE events ALTER PRIMARY KEY USING COLUMNS (product_id, owner, serial_number, ts, event_id) USING HASH;
> SHOW INDEX FROM events;
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+---------------+------------+--------------+-------------------------------------------------------------------+-----------+---------+-----------
events | events_pkey | false | 1 | crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | ASC | false | true
events | events_pkey | false | 2 | product_id | ASC | false | false
events | events_pkey | false | 3 | owner | ASC | false | false
events | events_pkey | false | 4 | serial_number | ASC | false | false
events | events_pkey | false | 5 | ts | ASC | false | false
events | events_pkey | false | 6 | event_id | ASC | false | false
events | events_pkey | false | 7 | data | N/A | true | false
events | events_ts_idx | true | 1 | crdb_internal_ts_shard_16 | ASC | false | true
events | events_ts_idx | true | 2 | ts | ASC | false | false
events | events_ts_idx | true | 3 | crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | ASC | false | true
events | events_ts_idx | true | 4 | product_id | ASC | false | true
events | events_ts_idx | true | 5 | owner | ASC | false | true
events | events_ts_idx | true | 6 | serial_number | ASC | false | true
events | events_ts_idx | true | 7 | event_id | ASC | false | true
(14 rows)
> SHOW COLUMNS FROM events;
column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
--------------------------------------------------------------------+-----------+-------------+----------------+-----------------------------------------------------------------------------------------------+-----------------------------+------------
product_id | INT8 | false | NULL | | {events_pkey,events_ts_idx} | false
owner | UUID | false | NULL | | {events_pkey,events_ts_idx} | false
serial_number | VARCHAR | false | NULL | | {events_pkey,events_ts_idx} | false
event_id | UUID | false | NULL | | {events_pkey,events_ts_idx} | false
ts | TIMESTAMP | false | NULL | | {events_pkey,events_ts_idx} | false
data | JSONB | true | NULL | | {events_pkey} | false
crdb_internal_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16) | {events_ts_idx} | true
crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | INT8 | false | NULL | mod(fnv32(crdb_internal.datums_to_bytes(event_id, owner, product_id, serial_number, ts)), 16) | {events_pkey,events_ts_idx} | true
(8 rows)
Note that the old primary key index becomes a secondary index, in this case, users_name_key. If you do not want the old primary key to become a secondary index when changing a primary key, you can use DROP CONSTRAINT/ADD CONSTRAINT instead.
