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 YugabyteDB Managed, and install the sample Northwind 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)

UNIQUE Constraint

The UNIQUE constraint allows you to ensure that values stored in columns are unique across rows in a table. During inserting new rows or updating existing ones, the UNIQUE constraint checks if the value is already in the table, in which case the change is rejected and an error is displayed.

When you add a UNIQUE constraint to one or more columns, YSQL automatically creates a unique index on these columns.

The following example creates a table with a UNIQUE constraint for the phone column:

CREATE TABLE employees (
  employee_no integer PRIMARY KEY,
  name text,
  department text,
  phone integer UNIQUE
);

The following example creates the same constraint for the same column of the same table, only as a table constraint:

CREATE TABLE employees (
  employee_no integer PRIMARY KEY,
  name text,
  department text,
  phone integer,
  UNIQUE(phone)
);

The following example creates a UNIQUE constraint on a group of columns in a new table:

CREATE TABLE employees (
  employee_no integer PRIMARY KEY,
  name text,
  department text,
  phone integer,
  email text
  UNIQUE(phone, email)
);

For additional examples, see Table with UNIQUE constraint.

Learn more