> ## 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](/docs/stable/constraints.md) 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](/docs/stable/not-null) in addition to the `UNIQUE` constraint. You can also achieve the same behavior through the table's [Primary Key](/docs/stable/primary-key.md).

* Columns with the `UNIQUE` constraint automatically have an [index](/docs/stable/indexes.md) 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](/docs/stable/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`](/docs/stable/alter-table#add-the-unique-constraint).

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

While CockroachDB process an [`ADD REGION`](/docs/stable/alter-database#add-region) or [`DROP REGION`](/docs/stable/alter-database#drop-region) statement on a particular database, creating or modifying an index will throw an error. Similarly, all [`ADD REGION`](/docs/stable/alter-database#add-region) and [`DROP REGION`](/docs/stable/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](/docs/stable/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](/docs/stable/keywords-and-identifiers.md#identifiers). |
| `column_name` | The name of the column you want to constrain. |
| `table_constraints` | Any other table-level [constraints](/docs/stable/constraints.md) 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](/docs/stable/data-types.md). |
| `column_constraints` | Any other column-level [constraints](/docs/stable/constraints.md) you want to apply to this column. |
| `column_def` | Definitions for any other columns in the table. |
| `table_constraints` | Any table-level [constraints](/docs/stable/constraints.md) 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](/docs/stable/constraints.md)
* [`DROP CONSTRAINT`](/docs/stable/alter-table#drop-constraint)
* [`CHECK` constraint](/docs/stable/check)
* [`DEFAULT` value constraint](/docs/stable/default-value)
* [Foreign key constraint](/docs/stable/foreign-key.md)
* [`NOT NULL` constraint](/docs/stable/not-null)
* [`PRIMARY` key constraint](/docs/stable/primary-key)
* [`SHOW CONSTRAINTS`](/docs/stable/show-constraints)