ALTER PRIMARY KEY statement is a subcommand of
ALTER TABLE that can be used to change the primary key of a table.
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
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 a
UNIQUEsecondary 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 existing
NOT NULLconstraint. To add a
NOT NULLconstraint to an existing column, use
ALTER 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.
||The name of the table with the primary key that you want to modify.|
||The name of the column(s) that you want to use for the primary key. These columns replace the current primary key column(s).|
||Creates a hash-sharded index with
Note:To enable hash-sharded indexes, set the
Warning:Interleaving data was deprecated in v20.2, disabled by default in v21.1, and permanently removed in v21.2. For details, see the v21.1 interleaving deprecation notice.
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
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
ADD CONSTRAINT instead.