intro
Learn everything about the PostgreSQL UNIQUE constraint and use it to ensure that values stored in a column or a group of columns are unique across rows in a table.
The PostgreSQL UNIQUE constraint makes sure that each record in our table is identifiable in its own unique way, preventing the creation of duplicate data and further protecting the evenness of our database.
In this guide, you will learn what this constrain is, how to use it, and when. Let’s dive in!
What Is the UNIQUE PostgreSQL Constraint?
The PostgreSQLUNIQUE constraint guarantees that all values in a column or a group of columns are distinct from one another. This means no two rows can have the same value(s) in the specified column(s).
The primary characteristics of a UNIQUE constraint are:
Unique constraints can be defined on a single column or a combination of multiple columns, known as a composite unique constraint. This flexibility allows us to tailor our data model to our specific requirements, assuring that unique business rules are enforced at the database level.
How to Use the PostgreSQL UNIQUE Constraint
Defining a UNIQUE PostgreSQL constraint is pretty straightforward. When it is applied on a column constraint in a CREATE TABLE query, the syntax is:
1
CREATE TABLE table_name (
2
column_name data_type UNIQUE
3
);
When written as a table constraint, it is so:
1
CREATE TABLE my_table (
2
column_name data_type,
3
UNIQUE (column_name)
4
);
You can also add a unique constraint to an existing table using the ALTER TABLE statement:
1
ALTER TABLE my_table
2
ADD UNIQUE (column);
If you want to specify a UNIQUE constraint for more than a column or a group of columns, write your query as a table constraint and ensure that the column names are separated with commas:
1
CREATE TABLE my_table (
2
column_1 data_type,
3
column_2 data_type,
4
column_3 data_type,
5
UNIQUE (column_1, column_2)
6
);
This indicates that, although any one of the columns need not be (and typically isn't) unique, the combination of values in the stated columns must be unique throughout the entire table. You may also call this style of application the composite UNIQUE constraint style.
PostgreSQL UNIQUE Violation Conditions
These are the scenarios where a UNIQUE constraint is violated in PostgreSQL:
UNIQUE Constraint in PostgreSQL: Use Cases
Now that you know the syntax behind PostgreSQL UNIQUE and how it works, you are ready to explore its use cases.
Keep it at the back of your mind that all demonstrations will be done in DbVisualizer—the database client with the highest user satisfaction in the market, and the only database client that is capable of doing this —Joking haha! Any other database client can take care of this.
Use Case #1 - Testing Validity of Unique Violation Rules
The statement below creates a person table with a UNIQUE constraint on the email column.
We know from our previous section that this same statement can be rewritten this way:
Previously, we said that the UNIQUE PostgreSQL constraint is violated and will throw an error if you try to insert a new record with a value in a UNIQUE column (or set of columns) that already exists in the table, right?
Let’s test this rule to see if indeed, PostgreSQL will throw this UNIQUE constraint violation error - we got all the time in the world, and the best database client on our side!
First, populate the person table with data with this statement👇🏽
1
INSERT INTO person(first_name,last_name,email)
2
VALUES('Desiree','Vance','deevance@dbvis.com');
Secondly, try to populate another row with data but ensure that the email is the same as the email in the data that was initially inserted into our table (deevance2dbvis.com).
1
INSERT INTO person(first_name,last_name,email)
2
VALUES('Chris','Hughton','deevance@dbvis.com');
And indeed, PostgreSQL issues an error.
Use Case #2 - Implementing a Unique Constraint on a Group of Columns
We’ve also learned that the unique constraints can be applied to a combination of columns and this is known as a composite unique constraint. This can be particularly useful when you need to ensure the uniqueness of a set of values, rather than a single column only.
Assuming you have a transactionstable where you are monitoring order details and want to ensure that every order is distinct using the combination of product_id and customer_id:
1
CREATE TABLE transactions (
2
id SERIAL PRIMARY KEY,
3
customer_id INT NOT NULL,
4
product_id INT NOT NULL,
5
quantity INT NOT NULL,
6
UNIQUE (customer_id, product_id)
7
);
With this composite unique constraint in your database, you can prevent a customer from placing multiple orders for the same product, preventing potential issues in your application.
DbVisualizer makes visualizing data inside of your database instance, no matter what kind of DBMS you find yourself using. The image below shows one of the features of DbVisualizer: the SQL DDL query viewer:
Use Case #3 - Adding A UNIQUE Constraint To An Existing Table
Time to take a look at a scenario where you may want to add a unique constraint to an existing column or group of columns.
Assume we already have a sales_2023_sourcea table. We’re going to add a unique constraint to the sales_2023_sourcea table using this statement:
1
ALTER TABLE sales_2023_sourcea ADD CONSTRAINT unique_saleid UNIQUE (saleid);
And if by any chance, we want to drop our PostgreSQL UNIQUE constraint, we would do this:
1
ALTER TABLE sales_2023_sourcea ADD CONSTRAINT unique_saleid UNIQUE (saleid);
Best Practices and Considerations
Consider the following best practices when working with UNIQUE constraints in PostgreSQL:
Extra: Nuances Between UNIQUE Constraint Vs Primary Keys
Both primary keys and UNIQUE constraints enforce the uniqueness of our database records. However, there are some subtle distinctions you must know:
| UNIQUE Constraints | Primary Keys |
|---|---|
Allow NULL values | Do not allow NULL values |
| Creates a non-clustered index which does not affect the physical order of the table data | Create a clustered index that affects the physical order of the table data |
A table can have multiple UNIQUE constraints | A table can have only one primary key constraint. |
NB: Only use the UNIQUE constraint when you need to ensure the uniqueness of your db records without the restrictions imposed by primary keys.
Conclusion
In this blog, we have looked at the UNIQUE constraint in PostgreSQL, its power syntax, how to use it and every other thing - not excluding the its violation rules. To better appreciate the power of this constraint, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality and full support for all database features. Download DbVisualizer for free now! It will surprise you with many things. Take an expo of all of our features, the Pro version of DbVisualizer is free for 21 days.
FAQ
What is the UNIQUE constraint in PostgreSQL?
The UNIQUE constraint in PostgreSQL ensures that the values in a column (or a set of columns) are unique across all rows in a table, meaning you cannot have duplicate values in the column(s) with this constraint.
How do I create a UNIQUE PostgreSQL constraint?
Creating it is simple. You can create a PostgreSQL UNIQUE constraint using any of the following ways:
During the creation of a new table:
1
CREATE TABLE users (
2
id SERIAL PRIMARY KEY,
3
email VARCHAR(255) UNIQUE,
4
username VARCHAR(50)
5
);
Adding it to an already existing table:
1
ALTER TABLE users
2
ADD UNIQUE (username);
Specifying it for a group of columns:
1
CREATE TABLE users (
2
id SERIAL PRIMARY KEY,
3
email VARCHAR(255),
4
username VARCHAR(50)
5
UNIQUE (email, username)
6
);
Can a UNIQUE constraint have NULL values?
Yes, a UNIQUE constraint in PostgreSQL allows NULL values. You can have multiple rows where all columns in the constraint are NULL, and it won't cause a violation error.
Can I have multiple UNIQUE constraints on a single table?
Yes, you can have multiple UNIQUE constraints on a single table in PostgreSQL. Each UNIQUE constraint applies to a specific column or a set of columns.
