ALTER PRIMARY KEY

On this page

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 data was deprecated in v20.2, disabled by default in v21.1, and permanently removed in v21.2. For details, see the interleaving deprecation notice.

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.

Pricing

Contact us

Sign In