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
The examples run on any YugabyteDB universe.
Local
YugabyteDB Aeon
YugabyteDB Anywhere
Set up a local cluster
If a local universe is currently running, first destroy it.
Start a local one-node universe with an RF of 1 by first creating a single node, as follows:
./bin/yugabyted start \
--advertise_address=127.0.0.1 \
--base_dir=${HOME}/var/node1 \
--cloud_location=aws.us-east-2.us-east-2a
After starting the yugabyted processes on all the nodes, configure the data placement constraint of the universe, as follows:
./bin/yugabyted configure data_placement --base_dir=${HOME}/var/node1 --fault_tolerance=zone
This command can be executed on any node where you already started YugabyteDB.
To check the status of a running multi-node universe, run the following command:
./bin/yugabyted status --base_dir=${HOME}/var/node1
Setup
To set up a universe, refer to Set up a YugabyteDB Anywhere universe.Setup
To set up a cluster, refer to Set up a YugabyteDB Aeon cluster.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