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?
Primary key
|
Unique Constraint
|
---|---|
None of the fields, which are part of the primary key, can contain zero value.
|
Some fields that are part of the uniqueness constraint may contain zero values if the combination of values is unique.
|
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.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Strengthening Financial Operations: How Enteros and Cloud FinOps Transform Balance Sheet Management and Root Cause Analysis
- 10 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Database Management with Enteros: Revolutionizing RevOps, AIOps, and DevOps on Azure
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…