Primary Key Constraint

The PRIMARY KEY specifies that the constrained columns’ values must uniquely identify each row. A table can only have one primary key, but it can have multiple .

You should explicitly define a table’s primary key in the statement. If you don’t define a primary key at table creation time, CockroachDB will create a rowid column that is NOT VISIBLE, use the as its default value, and use that as the table’s primary key.

You can change the primary key of an existing table with an statement, or by using and then in the same transaction. You cannot fully drop the PRIMARY KEY constraint on a table without replacing it as it provides an intrinsic structure to the table’s data.

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

Parameter	Description
table_name	The name of the table you’re creating.
column_name	The name of the Primary Key column. For , you can use the crdb_region column within a composite primary key in the event the original primary key may contain non-unique entries across multiple, unique regions.
column_type	The Primary Key column’s .
column_constraints	Any other column-level you want to apply to this column.
column_def	Definitions for any other columns in the table.
table_constraints	Any table-level you want to apply.

Example

> 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

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 .
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 you want to apply.

Example

> 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 , 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 and 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 .

We strongly recommend adding size limits to all , which includes columns in .

Values exceeding 1 MiB can lead to and cause significant performance degradation or even .

To add a size limit using :

CREATE TABLE name (first STRING(100), last STRING(100));

To add a size limit using :

SET enable_experimental_alter_column_type_general = true;

ALTER TABLE name ALTER first TYPE STRING(99);

Example

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

> INSERT INTO inventories VALUES (1, 1, 100);

> INSERT INTO inventories VALUES (1, 1, 200);

pq: duplicate key value (product_id,warehouse_id)=(1,1) violates unique constraint "primary"

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

• Issuing an statement. When you change a primary key with ALTER PRIMARY KEY, the old primary key index becomes a secondary index. This helps optimize the performance of queries that still filter on the old primary key column.
• Issuing an statement to drop the primary key, followed by an statement, in the same transaction, to add a new primary key. This replaces the existing primary key without creating a secondary index from the old primary key. For examples, see and .

You can use an statement without a if the primary key was not explicitly defined at , and the current .

See also