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);
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