Unique Constraint

On this page Carat arrow pointing down

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 <table 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 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, 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 and at the column level.

Table level

CREATE TABLE table_name ( column_table_def , CONSTRAINT name UNIQUE ( column_name , ) table_constraints )
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

icon/buttons/copy
> CREATE TABLE logon (
    login_id  INT PRIMARY KEY,
    customer_id   INT,
    logon_date    TIMESTAMP,
    UNIQUE (customer_id, logon_date)
  );

Column level

CREATE TABLE table_name ( column_name column_type UNIQUE column_constraints , column_table_def table_constraints ) )
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

icon/buttons/copy
> CREATE TABLE warehouses (
    warehouse_id    INT        PRIMARY KEY NOT NULL,
    warehouse_name  STRING(35) UNIQUE,
    location_id     INT
  );

Usage example

icon/buttons/copy
> CREATE TABLE IF NOT EXISTS logon (
    login_id INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    sales_id INT,
    UNIQUE (customer_id, sales_id)
  );
icon/buttons/copy
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (1, 2, 1);
icon/buttons/copy
> 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.

icon/buttons/copy
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (3, 2, NULL);
icon/buttons/copy
> INSERT INTO logon (login_id, customer_id, sales_id) VALUES (4, 2, NULL);
icon/buttons/copy
> SELECT customer_id, sales_id FROM logon;
+-------------+----------+
| customer_id | sales_id |
+-------------+----------+
|           2 |        1 |
|           2 | NULL     |
|           2 | NULL     |
+-------------+----------+

See also


Yes No
On this page

Yes No