PostgreSQL UNIQUE constraint

UNIQUE constraint

The PostgreSQL UNIQUE constraint ensures that the uniqueness of the values entered into a column or a field of a table.

The UNIQUE constraint in PostgreSQL can be applied as a column constraint or a group of column constraint or a table constraint.

The UNIQUE constraint in PostgreSQL violated when more than one row for a column or combination of columns which have been used as a unique constraint in a table. Two NULL values for a column in different rows is different and it does not violate the uniqueness of UNIQUE constraint.

When a UNIQUE constraint is adding, an index on a column or group of columns creates automatically.

PostgreSQL UNIQUE example

SQL

CREATE TABLE orders(
ord_no integer UNIQUE,
ord_date date,
item_name character(35),
item_grade character(1),
ord_qty numeric,
ord_amount numeric
);

The table structure

Constraint data dictionary

Explanation

The above example shows, the table orders have created in which the ord_no column is unique, can not contain any duplicate values.

PostgreSQL UNIQUE constraint group of columns

SQL

CREATE TABLE orders(
ord_no integer UNIQUE,
ord_date date,
item_name character(35) UNIQUE,
item_grade character(1),
ord_qty numeric,
ord_amount numeric
);

Output :

Constraint data dictionary

Explanation

The above example shows, the table orders have created with two unique constraints on two different columns with default constraint name and these two constraints stored in the data dictionary also have shown.

PostgreSQL UNIQUE constraint with constraint name

SQL

CREATE TABLE orders(
ord_no integer CONSTRAINT unq_ord_no UNIQUE,
ord_date date,
item_name character(35) CONSTRAINT unq_it_name UNIQUE,
item_grade character(1),
ord_qty numeric,
ord_amount numeric
);

Output :

Constraint data dictionary

Explanation

The above example shows, the table orders have created with two unique constraints on two different columns with two users define constraint names and these two constraints stored in the data dictionary also have shown.

PostgreSQL UNIQUE constraint as table constraints

SQL

CREATE TABLE orders(
ord_no integer,
ord_date date,
item_name character(35),
item_grade character(1),
ord_qty numeric,
ord_amount numeric,
UNIQUE (ord_no)
);

Output :

Constraint data dictionary

Explanation

The above example shows, the table orders have created with unique constraints on ord_no column as table constraints.

PostgreSQL UNIQUE constraint on group of columns as table constraints

SQL

CREATE TABLE orders(
ord_no integer ,
ord_date date,
item_name character(35),
item_grade character(1),
ord_qty numeric,
ord_amount numeric,
CONSTRAINT unq_ordno_itname UNIQUE(ord_no,item_name)
);

Output :

Constraint data dictionary

Explanation

The above example shows, the table orders have created with two unique constraints on two different columns with a user define constraint names as a table constraint and these two constraints stored in the data dictionary also have shown. The group of columns is unique for the whole table, it is not necessary that any of the columns must be unique.