Database Reference
In-Depth Information
The unique index
A unique index can be created on any column; it not only creates an index, but also
enforces uniqueness of the column. This is the most important index in a database
design as it ensures data integrity and provides performance enhancements. There
are multiple ways to create a unique index: using the
CREATE UNIQUE INDEX
command, by creating a unique constraint on the table, or by creating a primary key.
Here is an example of a unique index created by the
CREATE UNIQUE INDEX
command:
warehouse_db=# CREATE UNIQUE INDEX item_unique_idx ON item
(item_id);
CREATE INDEX
Time: 485.644 ms
The result can be seen using the following statement:
warehouse_db=# \d item_unique_idx;
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+-------
public | item_unique_idx | index | postgres | item
(1 row)
We have discussed that we can create a unique index explicitly using the
CREATE
UNIQUE INDEX
command and that it can be created implicitly by declaring a primary
key on a table. Here is an example of an implicit creation of a unique index by
creating a primary key on a table:
warehouse_db=# CREATE TABLE item
(
item_unique INTEGER PRIMARY KEY,
item_name TEXT,
item_price NUMERIC,
item_data TEXT
);
The result can be seen using the following statement:
warehouse_db=# \d item
Table "item"
Column | Type | Modifiers
----------------+-----------------------+-----------
item_unique | integer | not null
item_name | text |
item_price | numeric |
