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
This example uses the categories table from the Northwind sample database. Follow the steps to create a local cluster or in Yugabyte Cloud, and install the sample Northwind database.
- View the contents of the
categoriestable.
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
UNIQUEindex for thecategory_idcolumn in thecategoriestable.
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
CREATEstatement is executed, any attempt to insert a new category with an existingcategory_idwill 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_idand 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)