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 or a UNIQUE constraint defined, a corresponding UNIQUE index is created automatically.
Syntax
CREATE INDEX index_name ON table_name(column_list);
Example
-
Follow the steps to create a cluster locally or in Yugabyte Cloud.
-
Use the YCQL shell for local clusters, or Connect using Cloud shell for Yugabyte Cloud, to create a keyspace and a table.
ycqlsh> CREATE KEYSPACE yb_demo;
ycqlsh> USE yb_demo;
ycqlsh> CREATE TABLE employees(employee_no integer,name text,department text, PRIMARY KEY(employee_no));
- Create a
UNIQUEindex for thenamecolumn in theemployeestable to allow only unique names in your table.
CREATE UNIQUE INDEX index_employee_no ON employees(employee_no);
- Use the DESCRIBE INDEX command to verify the index creation.
ycqlsh:yb_demo> DESCRIBE INDEX index_name;
CREATE UNIQUE INDEX index_name ON yb_demo.employees (name) INCLUDE (employee_no)
WITH transactions = {'enabled': 'true'};
- Insert values into the table and verify that no duplicate
namesare 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_name
INSERT INTO employees(employee_no, name, department) VALUES (3, 'Bob', 'Engineering');
^^^^
(ql error -300)"
Learn more
For other examples, refer the Create a table with a unique index.