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 WITH BUCKET_COUNT |
Creates a hash-sharded index with n_buckets number of buckets.Note: To enable hash-sharded indexes, set the experimental_enable_hash_sharded_indexes session variable to on. |
opt_interleave |
Warning: Interleaving data was deprecated in v20.2, disabled by default in v21.1, and permanently removed in v21.2. |
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 "primary" PRIMARY KEY (id ASC),
| UNIQUE INDEX users_name_key (name ASC),
| FAMILY "primary" (name, email, id)
| )
(1 row)
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.
