Primary Key Constraint

Warning:
Cockroach Labs will stop providing Assistance Support for this version on May 10, 2022. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

The PRIMARY KEY constraint specifies that the constrained columns' values must uniquely identify each row.

Unlike other constraints which have very specific uses, the PRIMARY KEY constraint must be used for every table because it provides an intrinsic structure to the table's data.

A table's primary key should be explicitly defined in the CREATE TABLE statement. Tables can only have one primary key.

You can change the primary key of an existing table with an ALTER TABLE ... ALTER PRIMARY KEY statement, or by using DROP CONSTRAINT and then ADD CONSTRAINT in the same transaction.

Syntax

PRIMARY KEY constraints can be defined at the table level. However, if you only want the constraint to apply to a single column, it can be applied at the column level.

Column level

CREATE TABLE table_name ( column_name column_type PRIMARY KEY column_constraints , column_def table_constraints ) )
Parameter Description
table_name The name of the table you're creating.
column_name The name of the Primary Key column.
column_type The Primary Key column's data type.
column_constraints Any other column-level constraints you want to apply to this column.
column_def Definitions for any other columns in the table.
table_constraints Any table-level constraints you want to apply.

Example

icon/buttons/copy
> CREATE TABLE orders (
    order_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_date      TIMESTAMP NOT NULL,
    order_mode      STRING(8),
    customer_id     INT,
    order_status    INT
  );

Table level

CREATE TABLE table_name ( column_def , CONSTRAINT name PRIMARY KEY ( column_name , ) table_constraints )
Parameter Description
table_name The name of the table you're creating.
column_def Definitions for any other columns in the table.
name The name you want to use for the constraint, which must be unique to its table and follow these identifier rules.
column_name The name of the column you want to use as the PRIMARY KEY.

The order in which you list columns here affects the structure of the primary index.
table_constraints Any other table-level constraints you want to apply.

Example

icon/buttons/copy
> CREATE TABLE IF NOT EXISTS inventories (
    product_id        INT,
    warehouse_id      INT,
    quantity_on_hand  INT NOT NULL,
    PRIMARY KEY (product_id, warehouse_id)
  );

Details

The columns in the PRIMARY KEY constraint are used to create its primary index, which CockroachDB uses by default to access the table's data. This index does not take up additional disk space (unlike secondary indexes, which do) because CockroachDB uses the primary index to structure the table's data in the key-value layer. For more information, see our blog post SQL in CockroachDB: Mapping Table Data to Key-Value Storage.

To ensure each row has a unique identifier, the PRIMARY KEY constraint combines the properties of both the UNIQUE and NOT NULL constraints. The properties of both constraints are necessary to make sure each row's primary key columns contain distinct sets of values. The properties of the UNIQUE constraint ensure that each value is distinct from all other values. However, because NULL values never equal other NULL values, the UNIQUE constraint is not enough (two rows can appear the same if one of the values is NULL). To prevent the appearance of duplicated values, the PRIMARY KEY constraint also enforces the properties of the NOT NULL constraint.

For best practices, see Schema Design: Select primary key columns.

Example

icon/buttons/copy
> CREATE TABLE IF NOT EXISTS inventories (
    product_id        INT,
    warehouse_id      INT,
    quantity_on_hand  INT NOT NULL,
    PRIMARY KEY (product_id, warehouse_id)
  );
icon/buttons/copy
> INSERT INTO inventories VALUES (1, 1, 100);
icon/buttons/copy
> INSERT INTO inventories VALUES (1, 1, 200);
pq: duplicate key value (product_id,warehouse_id)=(1,1) violates unique constraint "primary"
icon/buttons/copy
> INSERT INTO inventories VALUES (1, NULL, 100);
pq: null value in column "warehouse_id" violates not-null constraint

Changing primary key columns

You can change the primary key of an existing table by doing one of the following:

Note:

You can use an ADD CONSTRAINT ... PRIMARY KEY statement without a DROP CONSTRAINT ... PRIMARY KEY if the primary key was not explicitly defined at table creation, and the current primary key is on rowid.

See also

YesYes NoNo