PostgreSQL Constraints

PostgreSQL Constraints: You’ll learn how to create, add, and remove unique constraints in PostgreSQL with syntax and examples.

What is a unique constraint in PostgreSQL?

A unique constraint is a single field or a combination of fields that uniquely define a record. Some fields may contain zero values if the combination of values is unique.

What is the difference between a unique constraint and a primary key?

Create a unique Constraint – use the CREATE TABLE operator

Syntax to create a unique constraint using the CREATE TABLE operator in PostgreSQL:

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 you want to create.
• column1, column2 – The columns you want to create in the table.
• constraint_name – The name of a unique constraint.
• uc_col1, uc_col2,… uc_col_n – The columns that make up the unique constraint.

Consider an example of how to create a unique limitation 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,
size integer,
notes varchar(200),
CONSTRAINT order_unique UNIQUE (order_id)
);

In this example, we created a unique restriction for the order_details table called order_unique. It consists of only one field, order_id.

We can 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,
size integer,
notes varchar(200),
CONSTRAINT order_date_unique UNIQUE (order_id, order_date)
);

Create a unique Constraint – using the ALTER TABLE operator

Syntax to create a unique constraint using ALTER TABLE in PostgreSQL:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2,... column_n);

• table_name – Name of the table to change. This is the table to which you want to add a unique constraint.
• constraint_name – The name of the unique constraint.
• column1, column2,… column_n – The columns that make up the unique constraint.

Let’s consider an example of how to add a unique limitation to an existing table in PostgreSQL using the ALTER TABLE operator.

ALTER TABLE order_details
ADD CONSTRAINT order_unique UNIQUE (order_id);

In this example, we have created a unique restriction for an existing order_details table with the name order_unique. It consists of a field with the name order_id.

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

Delete unique Constraint

Syntax to remove the unique restriction in PostgreSQL:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

• table_name – The name of the table to change. This is the table from which you want to remove the unique constraint.
• constraint_name – The name of the unique constraint to be removed.

Let’s consider an example of how to remove a unique limitation from a table in PostgreSQL.

ALTER TABLE order_details
DROP CONSTRAINT order_unique;

In this example, we discard a unique restriction on an order_details table named order_unique.

PostgreSQL: Creating Tables with Constraints | Course