ContextQMD
Back to Yugabyte Db

Unique indexes in YugabyteDB YSQL

docs/content/v2024.1/explore/ysql-language-features/indexes-constraints/unique-index-ysql.md

2026.1.0.0-b254.4 KB
Original Source

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

sql
CREATE UNIQUE INDEX index_name ON table_name(column_list);

Setup

The examples run on any YugabyteDB universe.

<!-- begin: nav tabs -->

{{<nav/tabs list="local,anywhere,cloud" active="local"/>}}

{{<nav/panels>}} {{<nav/panel name="local" active="true">}}

<!-- local cluster setup instructions -->

{{<setup/local numnodes="1" rf="1" >}}

{{</nav/panel>}}

{{<nav/panel name="anywhere">}} {{<setup/anywhere>}} {{</nav/panel>}} {{<nav/panel name="cloud">}}{{<setup/cloud>}}{{</nav/panel>}} {{</nav/panels>}}

<!-- end: nav tabs -->

This example uses the categories table from the Northwind sample database.

View the contents of the categories table:

sql
northwind=# SELECT * FROM categories  LIMIT 5;
caddyfile{.nocopy}
 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.

sql
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.

sql
northwind=# INSERT INTO categories(category_id, category_name, description) VALUES (1, 'Savories', 'Spicy chips and snacks');
sql{.nocopy}
ERROR:  duplicate key value violates unique constraint "categories_pkey"

Insert a row with a new category_id and verify its existence in the table.

sql
northwind=# INSERT INTO categories(category_id, category_name, description) VALUES (9, 'Savories', 'Spicy chips and snacks');
sql
northwind=# SELECT * FROM categories;
caddyfile{.nocopy}
 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

Learn more