> ## 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.
# Unique 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 `UNIQUE` constraint 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 in addition to the `UNIQUE` constraint. You can also achieve the same behavior through the table's Primary Key.
* Columns with the `UNIQUE` constraint automatically have an index created with the name `\_\_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 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`.
In multi-region deployments, most users should use `REGIONAL BY ROW` tables instead of explicit index partitioning. When you add an index to a `REGIONAL BY ROW` table, it is automatically partitioned on the `crdb\_region` column. Explicit index partitioning is not required.
While CockroachDB process an `ADD REGION` or `DROP REGION` statement on a particular database, creating or modifying an index will throw an error. Similarly, all `ADD REGION` and `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.
## Syntax
You can define `UNIQUE` constraints at the [table level](#table-level) and at the [column level](#column-level).
### Table level
| 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. |
| `column\_name` | The name of the column you want to constrain. |
| `table\_constraints` | Any other table-level 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
| 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. |
| `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 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);
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
+-------------+----------+
| customer_id | sales_id |
+-------------+----------+
| 2 | 1 |
| 2 | NULL |
| 2 | NULL |
+-------------+----------+
```
## See also
* Constraints
* `DROP CONSTRAINT`
* `CHECK` constraint
* `DEFAULT` value constraint
* Foreign key constraint
* `NOT NULL` constraint
* `PRIMARY` key constraint
* `SHOW CONSTRAINTS`