How to Add Unique Constraint in PostgreSQL: Syntax and Examples

In PostgreSQL, you add a unique constraint using the ALTER TABLE statement with ADD CONSTRAINT and UNIQUE keywords. This ensures that values in the specified column(s) are unique across the table.

Syntax

The basic syntax to add a unique constraint to an existing table is:

• ALTER TABLE table_name: Specifies the table to modify.
• ADD CONSTRAINT constraint_name: Names the unique constraint.
• UNIQUE (column1, column2, ...): Defines the column or columns that must have unique values.

sql

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

Example

This example shows how to add a unique constraint to the email column in a users table to prevent duplicate emails.

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

Output

CREATE TABLE
ALTER TABLE

Common Pitfalls

Common mistakes when adding unique constraints include:

• Trying to add a unique constraint on columns that already have duplicate values, which causes an error.
• Not naming the constraint, which can make it harder to manage later.
• Confusing UNIQUE constraints with PRIMARY KEY, which also enforces uniqueness but additionally disallows NULL values.

sql

/* Wrong: Adding unique constraint on column with duplicates */
-- Suppose 'email' column has duplicates
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

/* Right: Remove duplicates first or clean data before adding constraint */

Quick Reference

Command	Purpose
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column);	Add unique constraint to column(s)
CREATE TABLE ... (column datatype UNIQUE, ...);	Create table with unique constraint
ALTER TABLE table_name DROP CONSTRAINT constraint_name;	Remove unique constraint
SELECT * FROM pg_constraint WHERE contype = 'u';	List all unique constraints in database

Key Takeaways

Use ALTER TABLE with ADD CONSTRAINT and UNIQUE to add a unique constraint in PostgreSQL.

Ensure no duplicate values exist in the column(s) before adding the constraint.

Name your constraints for easier management and clarity.

Unique constraints allow NULLs but ensure no duplicate non-null values.

Use system catalogs like pg_constraint to view existing unique constraints.