The ALTER PRIMARY KEY statement is a subcommand of ALTER TABLE that can be used to change the primary key of a table.
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 |
Interleave table into parent object. Warning: Interleaving is deprecated in CockroachDB v20.2. For details, see INTERLEAVE IN PARENT Deprecation. |
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.
Make a single-column primary key composite for geo-partitioning
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 (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email STRING,
name STRING,
INDEX users_name_idx (name)
);
Now suppose that you want to expand your business from a single region into multiple regions. After you deploy your application in multiple regions, you consider geo-partitioning your data to minimize latency and optimize performance. In order to geo-partition the user database, you need to add a column specifying the location of the data (e.g., region):
> ALTER TABLE users ADD COLUMN region STRING NOT NULL;
When you geo-partition a database, you partition the database on a primary key column. The primary key of this table is still on id. Change the primary key to be composite, on region and id:
> ALTER TABLE users ALTER PRIMARY KEY USING COLUMNS (region, id);
The order of the primary key columns is important when geo-partitioning. For performance, always place the partition column first.
> SHOW CREATE TABLE users;
table_name | create_statement
-------------+-------------------------------------------------------------
users | CREATE TABLE users (
| id UUID NOT NULL DEFAULT gen_random_uuid(),
| email STRING NULL,
| name STRING NULL,
| region STRING NOT NULL,
| CONSTRAINT "primary" PRIMARY KEY (region ASC, id ASC),
| UNIQUE INDEX users_id_key (id ASC),
| INDEX users_name_idx (name ASC),
| FAMILY "primary" (id, email, name, region)
| )
(1 row)
Note that the old primary key index on id is now the secondary index users_id_key.
With the new primary key on region and id, the table is ready to be geo-partitioned:
> ALTER TABLE users PARTITION BY LIST (region) (
PARTITION us_west VALUES IN ('us_west'),
PARTITION us_east VALUES IN ('us_east')
);
> ALTER PARTITION us_west OF INDEX users@primary
CONFIGURE ZONE USING constraints = '[+region=us-west1]';
ALTER PARTITION us_east OF INDEX users@primary
CONFIGURE ZONE USING constraints = '[+region=us-east1]';
> SHOW PARTITIONS FROM TABLE users;
database_name | table_name | partition_name | parent_partition | column_names | index_name | partition_value | zone_config | full_zone_config
----------------+------------+----------------+------------------+--------------+---------------+-----------------+------------------------------------+--------------------------------------
movr | users | us_west | NULL | region | users@primary | ('us_west') | constraints = '[+region=us-west1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=us-west1]',
| | | | | | | | lease_preferences = '[]'
movr | users | us_east | NULL | region | users@primary | ('us_east') | constraints = '[+region=us-east1]' | range_min_bytes = 134217728,
| | | | | | | | range_max_bytes = 536870912,
| | | | | | | | gc.ttlseconds = 90000,
| | | | | | | | num_replicas = 3,
| | | | | | | | constraints = '[+region=us-east1]',
| | | | | | | | lease_preferences = '[]'
(2 rows)
The table is now geo-partitioned on the region column.
You now need to geo-partition any secondary indexes in the table. In order to geo-partition an index, the index must be prefixed by a column that can be used as a partitioning identifier (in this case, region). Currently, neither of the secondary indexes (i.e., users_id_key and users_name_idx) are prefixed by the region column, so they cannot be meaningfully geo-partitioned. Any secondary indexes that you want to keep must be dropped, recreated, and then partitioned.
Start by dropping both indexes:
> DROP INDEX users_id_key CASCADE;
DROP INDEX users_name_idx CASCADE;
You do not need to recreate the index on id with region. Both columns are already indexed by the new primary key.
Add region to the index on name:
> CREATE INDEX ON users(region, name);
Then geo-partition the index:
> ALTER INDEX users_region_name_idx PARTITION BY LIST (region) (
PARTITION us_west VALUES IN ('us_west'),
PARTITION us_east VALUES IN ('us_east')
);
> ALTER PARTITION us_west OF INDEX users@users_region_name_idx
CONFIGURE ZONE USING constraints = '[+region=us-west1]';
ALTER PARTITION us_east OF INDEX users@users_region_name_idx
CONFIGURE ZONE USING constraints = '[+region=us-east1]';
See also
Was this helpful?
