ALTER PRIMARY KEY

On this page Carat arrow pointing down

The ALTER PRIMARY KEY statement is a subcommand of ALTER TABLE that can be used to change the primary key of a table.

Note:

The ALTER PRIMARY KEY statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

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

ALTER TABLE IF EXISTS table_name ALTER PRIMARY KEY USING COLUMNS ( index_params ) USING HASH opt_with_storage_parameter_list

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 Creates a hash-sharded index.

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:

icon/buttons/copy
> 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:

icon/buttons/copy
> ALTER TABLE users ADD COLUMN id UUID NOT NULL DEFAULT gen_random_uuid();
icon/buttons/copy
> ALTER TABLE users ALTER PRIMARY KEY USING COLUMNS (id);
icon/buttons/copy
> 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 users_pkey PRIMARY KEY (id ASC),
             |     UNIQUE INDEX users_name_key (name ASC)
             | )
(1 row)

Alter an existing primary key to use hash sharding

Let's assume the events table already exists:

icon/buttons/copy
> CREATE TABLE events (
    product_id INT8,
    owner UUID,
    serial_number VARCHAR,
    event_id UUID,
    ts TIMESTAMP,
    data JSONB,
    PRIMARY KEY (product_id, owner, serial_number, ts, event_id),
    INDEX (ts) USING HASH
);

You can change an existing primary key to use hash sharding by adding the USING HASH clause at the end of the key definition:

icon/buttons/copy
> ALTER TABLE events ALTER PRIMARY KEY USING COLUMNS (product_id, owner, serial_number, ts, event_id) USING HASH;
icon/buttons/copy
> SHOW INDEX FROM events;
  table_name |  index_name   | non_unique | seq_in_index |                            column_name                            | direction | storing | implicit
-------------+---------------+------------+--------------+-------------------------------------------------------------------+-----------+---------+-----------
  events     | events_pkey   |   false    |            1 | crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | ASC       |  false  |   true
  events     | events_pkey   |   false    |            2 | product_id                                                        | ASC       |  false  |  false
  events     | events_pkey   |   false    |            3 | owner                                                             | ASC       |  false  |  false
  events     | events_pkey   |   false    |            4 | serial_number                                                     | ASC       |  false  |  false
  events     | events_pkey   |   false    |            5 | ts                                                                | ASC       |  false  |  false
  events     | events_pkey   |   false    |            6 | event_id                                                          | ASC       |  false  |  false
  events     | events_pkey   |   false    |            7 | data                                                              | N/A       |  true   |  false
  events     | events_ts_idx |    true    |            1 | crdb_internal_ts_shard_16                                         | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            2 | ts                                                                | ASC       |  false  |  false
  events     | events_ts_idx |    true    |            3 | crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            4 | product_id                                                        | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            5 | owner                                                             | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            6 | serial_number                                                     | ASC       |  false  |   true
  events     | events_ts_idx |    true    |            7 | event_id                                                          | ASC       |  false  |   true
(14 rows)
icon/buttons/copy
> SHOW COLUMNS FROM events;
                             column_name                            | data_type | is_nullable | column_default |                                     generation_expression                                     |           indices           | is_hidden
--------------------------------------------------------------------+-----------+-------------+----------------+-----------------------------------------------------------------------------------------------+-----------------------------+------------
  product_id                                                        | INT8      |    false    | NULL           |                                                                                               | {events_pkey,events_ts_idx} |   false
  owner                                                             | UUID      |    false    | NULL           |                                                                                               | {events_pkey,events_ts_idx} |   false
  serial_number                                                     | VARCHAR   |    false    | NULL           |                                                                                               | {events_pkey,events_ts_idx} |   false
  event_id                                                          | UUID      |    false    | NULL           |                                                                                               | {events_pkey,events_ts_idx} |   false
  ts                                                                | TIMESTAMP |    false    | NULL           |                                                                                               | {events_pkey,events_ts_idx} |   false
  data                                                              | JSONB     |    true     | NULL           |                                                                                               | {events_pkey}               |   false
  crdb_internal_ts_shard_16                                         | INT8      |    false    | NULL           | mod(fnv32(crdb_internal.datums_to_bytes(ts)), 16)                                             | {events_ts_idx}             |   true
  crdb_internal_event_id_owner_product_id_serial_number_ts_shard_16 | INT8      |    false    | NULL           | mod(fnv32(crdb_internal.datums_to_bytes(event_id, owner, product_id, serial_number, ts)), 16) | {events_pkey,events_ts_idx} |   true
(8 rows)

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.

See also


Yes No
On this page

Yes No