Text Size: Normal / Large

Home → Documentation → Manuals → PostgreSQL 8.1

PostgreSQL 8.1.13 Documentation
Prev	Fast Backward	Chapter 11. Indexes	Fast Forward	Next

11.5. Unique Indexes

Indexes may also be used to enforce uniqueness of a column's value, or the uniqueness of the combined values of more than one column.

CREATE UNIQUE INDEX name ON table (column [, ...]);

Currently, only B-tree indexes can be declared unique.

When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.

PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table. The index covers the columns that make up the primary key or unique columns (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Prev	Home	Next
Combining Multiple Indexes	Up	Indexes on Expressions

User Comments

Seph M. Soliman <postgresql AT mijav.dk>

02 Apr 2006 2:36:23

There is currently no equivalent to MySQL's INSERT [IGNORE] INTO or the REPLACE INTO statements. One has to make a stored procedure to handle the exception or an "intelligent" trigger.

Clint Pachl <clintpachl AT gmail.com>

13 Oct 2006 8:15:41

Alternate ways to create a unique index:

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  t1 TEXT NOT NULL,
  t2 TEXT NOT NULL,
  t3 TEXT NOT NULL,
  UNIQUE (t1, t2),
  CONSTRAINT test_t3_key UNIQUE (t3)
);

ALTER TABLE test ADD CONSTRAINT test_t1_key UNIQUE (t1);
ALTER TABLE test ADD UNIQUE (t1);

New comments cannot be added to old documentation versions.