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.
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
||Interleave table into parent object.
Warning:Interleaving was deprecated in CockroachDB v20.2, and is disabled by default in CockroachDB v21.1. For details, see
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.