> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Primary Key Constraint
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `PRIMARY KEY` constraint specifies that the constrained columns' values must uniquely identify each row. A table can only have one primary key, but it can have multiple unique constraints.
You should explicitly define a table's primary key in the `CREATE TABLE` 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 `unique_rowid()` function as its default value, and use that as the table's primary key.
You can [change the primary key](#changing-primary-key-columns) 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. 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](#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).
### Column level
| Parameter | Description |
| --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `table\_name` | The name of the table you're creating. |
| `column\_name` | The name of the Primary Key column. For multi-region tables, 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 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**
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 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**
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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](https://www.cockroachlabs.com/blog/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 Select primary key columns.
We **strongly recommend** adding size limits to all indexed columns, which includes columns in primary keys.
Values exceeding 1 MiB can lead to storage layer write amplification and cause significant performance degradation or even crashes due to OOMs (out of memory errors).
To add a size limit using `CREATE TABLE`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE name (first STRING(100), last STRING(100));
```
To add a size limit using `ALTER TABLE... ALTER COLUMN`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET enable_experimental_alter_column_type_general = true;
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE name ALTER first TYPE STRING(99);
```
## Example
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE IF NOT EXISTS inventories (
product_id INT,
warehouse_id INT,
quantity_on_hand INT NOT NULL,
PRIMARY KEY (product_id, warehouse_id)
);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO inventories VALUES (1, 1, 100);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO inventories VALUES (1, 1, 200);
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
pq: duplicate key value (product_id,warehouse_id)=(1,1) violates unique constraint "primary"
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO inventories VALUES (1, NULL, 100);
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 `ALTER TABLE... ALTER PRIMARY KEY` 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 `ALTER TABLE... DROP CONSTRAINT... PRIMARY KEY` statement to drop the primary key, followed by an `ALTER TABLE... ADD CONSTRAINT... PRIMARY KEY` 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 Add constraints and Drop constraints.
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
* Constraints
* `CHECK` constraint
* `DEFAULT` constraint
* `REFERENCES` constraint (Foreign Key)
* `PRIMARY KEY` constraint
* `NOT NULL` constraint
* `UNIQUE` constraint
* `SHOW CONSTRAINTS`
* `ALTER PRIMARY KEY`
* `ALTER TABLE`