PostgreSQL: How to Set Unique Constraint on a Table Column

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.