PostgreSQL Constraints

Preamble

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 Constraints?

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

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.