Enforcing Data Integrity with Unique Constraints in PostgreSQL

PostgreSQL Unique Constraint

A unique constraint in PostgreSQL ensures that all values in a specific column (or combination of columns) are unique within the table. This constraint is crucial for maintaining data integrity, as it prevents duplicate entries and enforces uniqueness rules on specified fields, such as email addresses, usernames, or IDs.

Syntax:

CREATE TABLE table_name (
    column1 data_type CONSTRAINT constraint_name UNIQUE,
    column2 data_type,
    ...
);

Alternatively, you can also add a unique constraint after the table is created:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1);

Explanation:

• constraint_name: An optional name for the constraint. If not specified, PostgreSQL will assign a default name.
• column1, column2, ...: Columns that need to maintain unique values.

Example 1: Unique Constraint on a Single Column

Code:

-- Creates a table with a unique constraint on the "email" column
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE, -- Email values must be unique
    username VARCHAR(50)
);

Explanation:

• Here, the email column in the users table has a unique constraint, ensuring that each email address entered is distinct across all records. If an email address is already in use, an error will occur when trying to insert a duplicate.

Example 2: Unique Constraint on Multiple Columns

Code:

-- Creates a table with a unique constraint on both "first_name" and "last_name" columns
CREATE TABLE people (
    person_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    CONSTRAINT unique_name UNIQUE (first_name, last_name) -- Combination of first and last name must be unique
);

Explanation:

• In this example, the unique constraint is applied to a combination of first_name and last_name. This means that while duplicate first names or last names alone are allowed, the same combination of both names cannot appear more than once in the people table.

Example 3: Adding a Unique Constraint to an Existing Table

Code:

-- Adds a unique constraint to the "phone_number" column in the "contacts" table
ALTER TABLE contacts
ADD CONSTRAINT unique_phone UNIQUE (phone_number); -- Ensures unique phone numbers

Explanation:

• Here, a unique constraint is added to the phone_number column in the contacts table. This will prevent duplicate phone numbers in the table from this point forward.

Important Notes:

1. Unique vs. Primary Key

• A primary key constraint inherently includes a unique constraint, but only one primary key is allowed per table. Multiple unique constraints can be applied to different columns or column combinations within the same table.

2. Handling Unique Violations

• PostgreSQL will raise an error if an insert or update operation violates a unique constraint. This error can be managed using an ON CONFLICT clause or by checking for uniqueness before insertion.

3. Unique Constraint and NULL Values

• PostgreSQL treats NULL values as distinct for the purposes of unique constraints, meaning multiple rows with NULLs in a unique column are allowed. This behavior may vary in other databases.

Summary:

Unique constraints are fundamental in PostgreSQL for enforcing data integrity by preventing duplicate values in specified columns or column combinations. They can be applied during table creation or added afterward with ALTER TABLE. Knowing when to use a unique constraint versus a primary key or an index is essential for database design.

All PostgreSQL Questions, Answers, and Code Snippets Collection.