Unique Constraints
This PostgreSQL tutorial explains how to create, add, and drop unique constraints in PostgreSQL with syntax and examples.
What is a unique constraint in PostgreSQL?
A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.
What is the difference between a unique constraint and a primary key?
|
Primary Key |
Unique Constraint |
|
None of the fields that are part of the primary key can contain a null value. |
Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique. |
Create unique Contraint - Using a CREATE TABLE statement
The syntax for creating a unique constraint using a CREATE TABLE statement in PostgreSQL is:
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);
table_name
The name of the table that you wish to create.
column1, column2
The columns that you wish to create in the table.
constraint_name
The name of the unique constraint.
uc_col1, uc_col2, ... uc_col_n
The columns that make up the unique constraint.
Example
Let's look at an example of how to create a unique constraint in PostgreSQL using the CREATE TABLE statement.
CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
order_id integer NOT NULL,
order_date date,
quantity integer,
notes varchar(200),
CONSTRAINT order_unique UNIQUE (order_id)
);
In this example, we've created a unique constraint on the order_details table called order_unique. It consists of only one field - the order_id field.
We could also create a unique constraint with more than one field as in the example below:
CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
order_id integer NOT NULL,
order_date date,
quantity integer,
notes varchar(200),
CONSTRAINT order_date_unique UNIQUE (order_id, order_date)
);
Create unique contraint - Using an ALTER TABLE statement
The syntax for creating a unique constraint using an ALTER TABLE statement in PostgreSQL is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
table_name
The name of the table to modify. This is the table that you wish to add a unique constraint to.
constraint_name
The name of the unique constraint.
column1, column2, ... column_n
The columns that make up the unique constraint.
Example
Let's look at an example of how to add a unique constraint to an existing table in PostgreSQL using the ALTER TABLE statement.
ALTER TABLE order_details
ADD CONSTRAINT order_unique UNIQUE (order_id);
In this example, we've created a unique constraint on the existing order_details table called order_unique. It consists of the field called order_id.
We could also create a unique constraint with more than one field as in the example below:
ALTER TABLE order_details
ADD CONSTRAINT order_date_unique UNIQUE (order_id, order_date);
Drop Unique Constraint
The syntax for dropping a unique constraint in PostgreSQL is:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
table_name
The name of the table to modify. This is the table that you wish to remove the unique constraint from.
constraint_name
The name of the unique constraint to remove.
Example
Let's look at an example of how to remove a unique constraint from a table in PostgreSQL.
ALTER TABLE order_details
DROP CONSTRAINT order_unique;
In this example, we're dropping a unique constraint on the order_details table called order_unique.