# ALTER PRIMARY KEY

New in v20.1: 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).
`opt_interleave` | You can potentially optimize query performance by interleaving tables, which changes how CockroachDB stores your data. Note: Hash-sharded indexes cannot be interleaved.
`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`.

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

- Constraints
- `ADD CONSTRAINT`
- `DROP CONSTRAINT`
- `ALTER TABLE`
- `SHOW JOBS`