Sling Academy
Home/PostgreSQL/PostgreSQL: How to Set Unique Constraint on a Table Column

PostgreSQL: How to Set Unique Constraint on a Table Column

Last updated: January 04, 2024

Overview

Ensuring data integrity is a fundamental aspect of managing databases. In PostgreSQL, the unique constraint prevents the duplication of values in a column, enforcing data uniqueness and integrity. This tutorial delves into setting unique constraints in PostgreSQL with practical examples.

Understanding Unique Constraints

By definition, a unique constraint is a rule applied to a column or a set of columns in a PostgreSQL table that ensures all values in the column are distinct. If a new record or an update to an existing record results in a duplicate entry in the column with a unique constraint, PostgreSQL will reject the change and return an error.

This constraint is crucial when you have identifiers or keys that must remain unique across the table, like emails, usernames, or custom IDs.

Setting a Unique Constraint During Table Creation

To start simple, let’s look at setting a unique constraint during the creation of a table.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE,
  email VARCHAR(255)
);

In the code above, the username column will not accept duplicate values. If you try to insert a new user with a username that’s already in use, PostgreSQL will return an error.

Adding a Unique Constraint to an Existing Column

Perhaps you already have a table and decide to enforce uniqueness on one of its columns. The following command shows how to add a unique constraint to the email column of the users table:

ALTER TABLE users
  ADD CONSTRAINT unique_email
  UNIQUE (email);

Note that if there are existing duplicates in the column, PostgreSQL won’t let you add the constraint until those duplicates are resolved.

Creating a Composite Unique Constraint

You can also set a unique constraint on a combination of columns, ensuring that the specific tuple of values is unique across the table.

CREATE TABLE reservations (
  room_id INT,
  reserved_date DATE,
  ... 
);

Now, you want both room_id and reserved_date together to be unique—we must not have two reservations for the same room on the same day:

ALTER TABLE reservations
  ADD CONSTRAINT unique_reservation
  UNIQUE (room_id, reserved_date);

This creates a composite key, where the combination of room ID and reservation date must be unique.

Enforcing Unique Constraint with Indexes

In PostgreSQL, unique constraints are implemented through unique indexes. It means you can create a unique index to achieve the same result.

CREATE UNIQUE INDEX unique_username
  ON users(username);

This method is particularly useful for adding constraints using specific index methods, like GIN or GiST.

Handling Violations Gracefully: ON CONFLICT Clause

When a unique constraint violation occurs, it’s important to handle it properly. PostgreSQL offers the ON CONFLICT clause, which gives you a way to specify alternative actions when a conflict is detected.

INSERT INTO users (username, email)
VALUES ('newuser', '[email protected]')
ON CONFLICT (username)
DO NOTHING;

-- Or to update an existing entry

INSERT INTO users (username, email)
VALUES ('newuser', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email;

The DO NOTHING action simply ignores the failed insertion. With DO UPDATE, you can update the conflicting row instead.

Removing a Unique Constraint

There may come a time when you need to drop a unique constraint from a table. You can do this as follows:

ALTER TABLE users
  DROP CONSTRAINT unique_email;

Just as when adding a constraint, you must ensure that the action does not lead to the violation of database integrity.

Unique Constraint and NULL Values

It’s worth noting that NULL values are considered distinct in a unique constraint context. This means you can have multiple rows with NULL values in a column with a unique constraint applied to it.

Monitoring Constraints with PostgreSQL Catalog

The system catalog in PostgreSQL stores information about all the constraints in the database. The following query lets you check for all unique constraints in a database:

SELECT conname, conrelid::regclass
FROM pg_constraint
WHERE contype = 'u';

This query looks in the pg_constraint system table for constraints of type ‘u’, which are unique constraints.

Advanced Usage: Conditional Unique Constraints

You can have partial unique constraints that apply only to a subset of rows, defined by some condition. This is achieved using partial indexes:

CREATE UNIQUE INDEX unique_active_users
  ON users (username)
  WHERE account_status = 'active';

In this example, the unique constraint on usernames would only apply to active users. If someone deactivates their account, their username could be used by a new account, provided the system handles such cases.

Conclusion

Implementing unique constraints in PostgreSQL ensures your data stays distinct where needed, enhancing integrity and consistency across your database. Whether you’re creating a new table, adjusting an existing one, or dealing with complex scenarios, PostgreSQL gives you flexible tools to manage these constraints effectively. Remember to handle constraint violations gracefully using the ON CONFLICT clause and keep your database’s design and rules up to date.

Next Article: How to Set Unsigned Integer in PostgreSQL Table Column

Previous Article: PostgreSQL: How to Set Index on a Table Column

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB