| title | summary | toc |
|---|---|---|
ALTER PRIMARY KEY |
Use the ALTER PRIMARY KEY statement to change the primary key of a table. |
true |
The ALTER PRIMARY KEY statement is a subcommand of ALTER TABLE that can be used to change the primary key of a table.
-
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.
{{site.data.alerts.callout_success}}
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.
{{site.data.alerts.end}}
| 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). |
opt_interleave |
You can potentially optimize query performance by interleaving tables, which changes how CockroachDB stores your data. {{site.data.alerts.callout_info}}Hash-sharded indexes cannot be interleaved.{{site.data.alerts.end}} |
USING HASH WITH BUCKET COUNT |
Creates a hash-sharded index with n_buckets number of buckets.{{site.data.alerts.callout_info}}To enable hash-sharded indexes, set the experimental_enable_hash_sharded_indexes session variable to on.{{site.data.alerts.end}} |
The user must have the CREATE privilege on a table to alter its primary key.
{% include {{ page.version.version }}/misc/schema-change-view-job.md %}
Suppose that you are storing the data for users of your application in a table called users, defined by the following CREATE TABLE statement:
{% include copy-clipboard.html %}
> 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:
{% include copy-clipboard.html %}
> ALTER TABLE users ADD COLUMN id UUID NOT NULL DEFAULT gen_random_uuid();{% include copy-clipboard.html %}
> ALTER TABLE users ALTER PRIMARY KEY USING COLUMNS (id);{% include copy-clipboard.html %}
> 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.
Suppose that you are storing the data for users of your application in a table called users, defined by the following CREATE TABLE statement:
{% include copy-clipboard.html %}
> 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):
{% include copy-clipboard.html %}
> 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:
{% include copy-clipboard.html %}
> ALTER TABLE users ALTER PRIMARY KEY USING COLUMNS (region, id);{{site.data.alerts.callout_info}} The order of the primary key columns is important when geo-partitioning. For performance, always place the partition column first. {{site.data.alerts.end}}
{% include copy-clipboard.html %}
> 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:
{% include copy-clipboard.html %}
> ALTER TABLE users PARTITION BY LIST (region) (
PARTITION us_west VALUES IN ('us_west'),
PARTITION us_east VALUES IN ('us_east')
);{% include copy-clipboard.html %}
> 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]';{% include copy-clipboard.html %}
> 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 can't be meaningfully geo-partitioned. Any secondary indexes that you want to keep must be dropped, recreated, and then partitioned.
Start by dropping both indexes:
{% include copy-clipboard.html %}
> DROP INDEX users_id_key CASCADE;
DROP INDEX users_name_idx CASCADE;You don't 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:
{% include copy-clipboard.html %}
> CREATE INDEX ON users(region, name);Then geo-partition the index:
{% include copy-clipboard.html %}
> ALTER INDEX users_region_name_idx PARTITION BY LIST (region) (
PARTITION us_west VALUES IN ('us_west'),
PARTITION us_east VALUES IN ('us_east')
);{% include copy-clipboard.html %}
> 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]';