> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Unique Constraint

The `UNIQUE` [constraint](https://docs.cockroachlabs.com/docs/v24.2/constraints) specifies that each non-`NULL` value in the constrained column must be unique.

## Details

* You can insert `NULL` values into columns with the `UNIQUE` constraint because `NULL` is the absence of a value, so it is never equal to other `NULL` values and not considered a duplicate value. This means that it's possible to insert rows that appear to be duplicates if one of the values is `NULL`.

  If you need to strictly enforce uniqueness, use the [`NOT NULL` constraint](https://docs.cockroachlabs.com/docs/v24.2/not-null) in addition to the `UNIQUE` constraint. You can also achieve the same behavior through the table's [Primary Key](https://docs.cockroachlabs.com/docs/v24.2/primary-key).

* Columns with the `UNIQUE` constraint automatically have an [index](https://docs.cockroachlabs.com/docs/v24.2/indexes) created with the name `_<columns_key`. To avoid having two identical indexes, you should not create indexes that exactly match the `UNIQUE` constraint's columns and order.

  The `UNIQUE` constraint depends on the automatically created index, so dropping the index also drops the `UNIQUE` constraint. Conversely, [dropping the `UNIQUE` constraint](https://docs.cockroachlabs.com/docs/v24.2/alter-table#drop-constraint) also drops the automatically created index.

* When using the `UNIQUE` constraint on multiple columns, the collective values of the columns must be unique. This *does not* mean that each value in each column must be unique, as if you had applied the `UNIQUE` constraint to each column individually.

* You can define the `UNIQUE` constraint when you [create a table](#syntax), or you can add it to an existing table through [`ADD CONSTRAINT`](https://docs.cockroachlabs.com/docs/v24.2/alter-table#add-the-unique-constraint).

In [multi-region deployments](https://docs.cockroachlabs.com/docs/v24.2/multiregion-overview), most users should use [`REGIONAL BY ROW` tables](https://docs.cockroachlabs.com/docs/v24.2/table-localities#regional-by-row-tables) instead of explicit index [partitioning](https://docs.cockroachlabs.com/docs/v24.2/partitioning). When you add an index to a `REGIONAL BY ROW` table, it is automatically partitioned on the [`crdb_region` column](https://docs.cockroachlabs.com/docs/v24.2/alter-table#crdb_region). Explicit index partitioning is not required.

While CockroachDB process an [`ADD REGION`](https://docs.cockroachlabs.com/docs/v24.2/alter-database#add-region) or [`DROP REGION`](https://docs.cockroachlabs.com/docs/v24.2/alter-database#drop-region) statement on a particular database, creating or modifying an index will throw an error. Similarly, all [`ADD REGION`](https://docs.cockroachlabs.com/docs/v24.2/alter-database#add-region) and [`DROP REGION`](https://docs.cockroachlabs.com/docs/v24.2/alter-database#drop-region) statements will be blocked while an index is being modified on a `REGIONAL BY ROW` table within the same database.

For an example that uses unique indexes, see [Add a unique index to a `REGIONAL BY ROW` table](https://docs.cockroachlabs.com/docs/v24.2/alter-table#add-a-unique-index-to-a-regional-by-row-table).

## Syntax

You can define `UNIQUE` constraints at the [table level](#table-level) and at the [column level](#column-level).

### Table level

![unique\_table\_level syntax diagram](https://mintcdn.com/cockroachlabs/M6E8QStE4G5ISd4O/images/sql-diagrams/common/unique_table_level.svg?fit=max\&auto=format\&n=M6E8QStE4G5ISd4O\&q=85\&s=ebb42c73885d001ba9849a097a140c7f)

| Parameter | Description |
| - | - |
| `table_name` | The name of the table you are 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](https://docs.cockroachlabs.com/docs/v24.2/keywords-and-identifiers#identifiers). |
| `column_name` | The name of the column you want to constrain. |
| `table_constraints` | Any other table-level [constraints](https://docs.cockroachlabs.com/docs/v24.2/constraints) you want to apply. |

**Example**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE logon (
    login_id  INT PRIMARY KEY,
    customer_id   INT,
    logon_date    TIMESTAMP,
    UNIQUE (customer_id, logon_date)
  );
```

### Column level

![unique\_column\_level syntax diagram](https://mintcdn.com/cockroachlabs/M6E8QStE4G5ISd4O/images/sql-diagrams/common/unique_column_level.svg?fit=max\&auto=format\&n=M6E8QStE4G5ISd4O\&q=85\&s=682ee2d93c7cfd49c5b17e976660c973)

| Parameter | Description |
| - | - |
| `table_name` | The name of the table you are creating. |
| `column_name` | The name of the constrained column. |
| `column_type` | The constrained column's [data type](https://docs.cockroachlabs.com/docs/v24.2/data-types). |
| `column_constraints` | Any other column-level [constraints](https://docs.cockroachlabs.com/docs/v24.2/constraints) you want to apply to this column. |
| `column_def` | Definitions for any other columns in the table. |
| `table_constraints` | Any table-level [constraints](https://docs.cockroachlabs.com/docs/v24.2/constraints) you want to apply. |

**Example**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE warehouses (
    warehouse_id    INT        PRIMARY KEY NOT NULL,
    warehouse_name  STRING(35) UNIQUE,
    location_id     INT
  );
```

## Usage example

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE IF NOT EXISTS logon (
    login_id INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    sales_id INT,
    UNIQUE (customer_id, sales_id)
  );
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (1, 2, 1);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (2, 2, 1);
```

```
duplicate key value (customer_id,sales_id)=(2,1) violates unique constraint "logon_customer_id_sales_id_key"
```

As mentioned in the [details](#details) above, it is possible when using the `UNIQUE` constraint alone to insert `NULL` values in a way that causes rows to appear to have rows with duplicate values.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (3, 2, NULL);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (4, 2, NULL);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT customer_id, sales_id FROM logon;
```

```
+-------------+----------+
| customer_id | sales_id |
+-------------+----------+
|           2 |        1 |
|           2 | NULL     |
|           2 | NULL     |
+-------------+----------+
```

## See also

* [Constraints](https://docs.cockroachlabs.com/docs/v24.2/constraints)
* [`DROP CONSTRAINT`](https://docs.cockroachlabs.com/docs/v24.2/alter-table#drop-constraint)
* [`CHECK` constraint](https://docs.cockroachlabs.com/docs/v24.2/check)
* [`DEFAULT` value constraint](https://docs.cockroachlabs.com/docs/v24.2/default-value)
* [Foreign key constraint](https://docs.cockroachlabs.com/docs/v24.2/foreign-key)
* [`NOT NULL` constraint](https://docs.cockroachlabs.com/docs/v24.2/not-null)
* [`PRIMARY` key constraint](https://docs.cockroachlabs.com/docs/v24.2/primary-key)
* [`SHOW CONSTRAINTS`](https://docs.cockroachlabs.com/docs/v24.2/show-constraints)