Unique indexes
If you need values in some of the columns to be unique, you can specify your index as UNIQUE
.
When a unique index is applied to two or more columns, the combined values in these columns can't be duplicated in multiple rows. Note that because a NULL
value is treated as a distinct value, you can have multiple NULL
values in a column with a unique index.
If a table has a primary key defined, a corresponding unique index is created automatically.
Before you start
The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.
Syntax
CREATE UNIQUE INDEX index_name ON table_name(column_list);
Example
-
Create a keyspace and a table as follows:
ycqlsh> CREATE KEYSPACE yb_demo; ycqlsh> USE yb_demo; ycqlsh> CREATE TABLE employees(employee_no integer,name text,department text, PRIMARY KEY(employee_no)) WITH transactions = {'enabled': 'true'};
-
Create a
UNIQUE
index for thename
column in theemployees
table to allow only unique names in your table.CREATE UNIQUE INDEX index_employee_name ON employees(name);
-
Use the DESCRIBE INDEX command to verify the index creation.
ycqlsh:yb_demo> DESCRIBE INDEX index_employee_name;
CREATE UNIQUE INDEX index_employee_name ON yb_demo.employees (name) INCLUDE (employee_no) WITH transactions = {'enabled': 'true'};
-
Insert values into the table and verify that no duplicate names are created.
ycqlsh:yb_demo> INSERT INTO employees(employee_no, name, department) VALUES (1, 'John', 'Sales'); ycqlsh:yb_demo> INSERT INTO employees(employee_no, name, department) VALUES (2, 'Bob', 'Marketing'); ycqlsh:yb_demo> INSERT INTO employees(employee_no, name, department) VALUES (3, 'Bob', 'Engineering');
InvalidRequest: Error from server: code=2200 [Invalid query] message="Execution Error. Duplicate value disallowed by unique index index_employee_name INSERT INTO employees(employee_no, name, department) VALUES (3, 'Bob', 'Engineering'); ^^^^ (ql error -300)"