Unique indexes
If you need values in some of the columns to be unique, you can create a UNIQUE index on that column. The behavior is more of a constraint than an index. If a table has a primary key or a UNIQUE constraint defined, a corresponding unique index is created automatically.
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.
Syntax
CREATE UNIQUE INDEX index_name ON table_name(column_list) [ NULLS [ NOT ] DISTINCT ];
When creating an unique index, by default, null values are not considered equal, allowing multiple nulls in the column. The NULLS NOT DISTINCT option modifies this and causes the index to treat nulls as equal.
Setup
Before you start
The examples will run on any YugabyteDB universe.
To create and connect to 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);
Now, any attempt to insert a new category with an existing category_id results 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