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 UNIQUE INDEX index_name ON table_name(column_list);
Example
Before you start
The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.
This example uses the categories table from the Northwind sample database.
View the contents of the categories table:
northwind=# SELECT * FROM categories LIMIT 5;
category_id | category_name | description | picture
-------------+----------------+------------------------------------------------------------+---------
4 | Dairy Products | Cheeses | \x
1 | Beverages | Soft drinks, coffees, teas, beers, and ales | \x
2 | Condiments | Sweet and savory sauces, relishes, spreads, and seasonings | \x
7 | Produce | Dried fruit and bean curd | \x
3 | Confections | Desserts, candies, and sweet breads | \x
(5 rows)
Create a UNIQUE index for the category_id column in the categories table.
northwind=# CREATE UNIQUE INDEX index_category_id
ON categories(category_id);
List the index created using the following command:
northwind=# SELECT indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'categories';
indexname | indexdef
-------------------+-----------------------------------------------------------------------------------------
categories_pkey | CREATE UNIQUE INDEX categories_pkey ON public.categories USING lsm (category_id HASH)
index_category_id | CREATE UNIQUE INDEX index_category_id ON public.categories USING lsm (category_id HASH)
(2 rows)
After the CREATE statement is executed, any attempt to insert a new category with an existing category_id will result in an error.
northwind=# INSERT INTO categories(category_id, category_name, description) VALUES (1, 'Savories', 'Spicy chips and snacks');
ERROR: duplicate key value violates unique constraint "categories_pkey"
Insert a row with a new category_id and verify its existence in the table.
northwind=# INSERT INTO categories(category_id, category_name, description) VALUES (9, 'Savories', 'Spicy chips and snacks');
northwind=# SELECT * FROM categories;
category_id | category_name | description | picture
-------------+----------------+------------------------------------------------------------+---------
4 | Dairy Products | Cheeses | \x
1 | Beverages | Soft drinks, coffees, teas, beers, and ales | \x
2 | Condiments | Sweet and savory sauces, relishes, spreads, and seasonings | \x
7 | Produce | Dried fruit and bean curd | \x
9 | Savories | Spicy chips and snacks |
3 | Confections | Desserts, candies, and sweet breads | \x
8 | Seafood | Seaweed and fish | \x
5 | Grains/Cereals | Breads, crackers, pasta, and cereal | \x
6 | Meat/Poultry | Prepared meats | \x
(9 rows)