ALTER PRIMARY KEY

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 KEY might 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 UNIQUE secondary index. This helps optimize the performance of queries that still filter on the old primary key column.
ALTER PRIMARY KEY does 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 KEY will 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 KEY must have an existing NOT NULL constraint. To add a NOT NULL constraint to an existing column, use ALTER TABLE ... ALTER COLUMN ... SET NOT NULL.

Tip:

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 was deprecated in CockroachDB v20.2, and is disabled by default in CockroachDB v21.1. 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);

Note:

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 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:

> 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:

> 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]';

The Most Highly Evolved Database on the Planet

CockroachDB

CockroachCloud

Get to know CockroachDB