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 theUNIQUE
constraint becauseNULL
is the absence of a value, so it is never equal to otherNULL
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 isNULL
.If you need to strictly enforce uniqueness, use the
NOT NULL
constraint in addition to theUNIQUE
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<table name>_<columns>_key
. To avoid having two identical indexes, you should not create indexes that exactly match theUNIQUE
constraint's columns and order.The
UNIQUE
constraint depends on the automatically created index, so dropping the index also drops theUNIQUE
constraint. Conversely, dropping theUNIQUE
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 theUNIQUE
constraint to each column individually.You can define the
UNIQUE
constraint when you create a table, or you can add it to an existing table throughADD 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 and at the 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
> 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
> CREATE TABLE warehouses (
warehouse_id INT PRIMARY KEY NOT NULL,
warehouse_name STRING(35) UNIQUE,
location_id INT
);
Usage example
> CREATE TABLE IF NOT EXISTS logon (
login_id INT PRIMARY KEY,
customer_id INT NOT NULL,
sales_id INT,
UNIQUE (customer_id, sales_id)
);
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (1, 2, 1);
> 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 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.
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (3, 2, NULL);
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (4, 2, NULL);
> SELECT customer_id, sales_id FROM logon;
+-------------+----------+
| customer_id | sales_id |
+-------------+----------+
| 2 | 1 |
| 2 | NULL |
| 2 | NULL |
+-------------+----------+