PostgreSQL Unique Constraints
Introduction
Unique constraints are an essential feature in PostgreSQL that help maintain data integrity by ensuring no duplicate values exist in a column or a group of columns. They act as rules that the database enforces automatically, preventing operations that would violate these rules.
In this tutorial, you'll learn how to create, manage, and work with unique constraints in PostgreSQL. Understanding unique constraints is crucial for designing robust database schemas that protect your data's consistency and reliability.
What is a Unique Constraint?
A unique constraint is a rule that prevents duplicate values in a column or a combination of columns in a table. When a unique constraint is applied:
- The database rejects any INSERT or UPDATE operations that would create duplicate values
- Each value (or combination of values) must be unique or NULL
- Unlike primary keys, unique constraints allow NULL values (and multiple NULL values are permitted)
Creating Tables with Unique Constraints
Basic Syntax
There are two main ways to define unique constraints in PostgreSQL:
1. Column-level constraint
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE,
full_name VARCHAR(100)
);
2. Table-level constraint
CREATE TABLE products (
product_id SERIAL,
product_code VARCHAR(20),
category_id INTEGER,
product_name VARCHAR(100),
PRIMARY KEY (product_id),
UNIQUE (product_code)
);
For multi-column unique constraints, you must use the table-level constraint syntax:
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
unit_price NUMERIC(10,2),
PRIMARY KEY (order_id, product_id),
UNIQUE (order_id, product_id)
);
Adding Unique Constraints to Existing Tables
If you already have a table and want to add a unique constraint, you can use the ALTER TABLE command:
ALTER TABLE customers
ADD CONSTRAINT unique_email UNIQUE (email);
For multi-column constraints:
ALTER TABLE employees
ADD CONSTRAINT unique_department_title UNIQUE (department_id, job_title);
Naming Your Constraints
It's a good practice to explicitly name your constraints. If you don't provide a name, PostgreSQL will generate one automatically, but having descriptive names makes database maintenance easier:
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_email VARCHAR(100) CONSTRAINT unique_student_email UNIQUE,
registration_number VARCHAR(20) CONSTRAINT unique_registration_number UNIQUE
);
Practical Examples
Example 1: User Registration System
Let's imagine we're building a user registration system where each user must have a unique username and email address:
-- Create the users table with unique constraints
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) CONSTRAINT unique_username UNIQUE NOT NULL,
email VARCHAR(100) CONSTRAINT unique_email UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Let's insert some valid users
INSERT INTO users (username, email, password)
VALUES
('john_doe', '[email protected]', 'hashed_password1'),
('jane_smith', '[email protected]', 'hashed_password2');
Now, let's see what happens when we try to insert a duplicate username:
-- This will fail due to unique constraint violation
INSERT INTO users (username, email, password)
VALUES ('john_doe', '[email protected]', 'hashed_password3');
Output:
ERROR: duplicate key value violates unique constraint "unique_username"
DETAIL: Key (username)=(john_doe) already exists.
Similarly, attempting to insert a duplicate email will fail:
-- This will also fail due to unique constraint violation
INSERT INTO users (username, email, password)
VALUES ('new_user', '[email protected]', 'hashed_password4');
Output:
ERROR: duplicate key value violates unique constraint "unique_email"
DETAIL: Key (email)=([email protected]) already exists.
Example 2: Product Inventory with Composite Unique Constraint
For a product inventory system, we might want to ensure that each product has a unique combination of SKU code and warehouse location:
-- Create inventory table with a composite unique constraint
CREATE TABLE inventory (
inventory_id SERIAL PRIMARY KEY,
product_sku VARCHAR(20),
warehouse_id INTEGER,
quantity INTEGER NOT NULL DEFAULT 0,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT unique_product_location UNIQUE (product_sku, warehouse_id)
);
-- Insert some inventory records
INSERT INTO inventory (product_sku, warehouse_id, quantity)
VALUES
('SKU001', 1, 100),
('SKU001', 2, 50), -- Same SKU but different warehouse is OK
('SKU002', 1, 75);
Trying to insert a duplicate combination will fail:
-- This will fail because SKU001 already exists in warehouse 1
INSERT INTO inventory (product_sku, warehouse_id, quantity)
VALUES ('SKU001', 1, 25);
Output:
ERROR: duplicate key value violates unique constraint "unique_product_location"
DETAIL: Key (product_sku, warehouse_id)=(SKU001, 1) already exists.
Removing Unique Constraints
If you need to remove a unique constraint, you can use the ALTER TABLE command:
ALTER TABLE users
DROP CONSTRAINT unique_email;
Unique Constraints vs. Unique Indexes
When you create a unique constraint, PostgreSQL automatically creates a unique index to enforce it. This index is used to quickly check for duplicate values and also improves query performance when searching by the constrained columns.
You can also create a unique index directly:
CREATE UNIQUE INDEX idx_users_username ON users(username);
The main difference is that constraints are part of the table's definition and represent business rules, while indexes are physical structures that improve performance. For most cases, using constraints is preferred because they clearly express the data integrity rules.
NULL Values and Unique Constraints
Unlike primary keys, unique constraints allow NULL values, and interestingly, multiple NULL values don't violate the constraint. PostgreSQL treats each NULL as distinct from other NULLs for the purpose of unique constraints.
CREATE TABLE contacts (
contact_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
phone VARCHAR(20) UNIQUE,
email VARCHAR(100) UNIQUE
);
-- This works fine
INSERT INTO contacts (name, phone, email)
VALUES
('Alice', '123-456-7890', '[email protected]'),
('Bob', NULL, '[email protected]'),
('Charlie', NULL, '[email protected]'); -- Multiple NULLs in phone are allowed
Error Handling
When a unique constraint violation occurs, your application needs to handle it gracefully. In PostgreSQL, the error code for unique violations is 23505. Here's how you might handle it in a simple application:
DO $$
BEGIN
-- Try to insert a duplicate
INSERT INTO users (username, email, password)
VALUES ('john_doe', '[email protected]', 'password123');
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'Username already exists. Please choose a different username.';
END $$;
Partial Unique Constraints (PostgreSQL 9.0+)
PostgreSQL allows you to create partial unique constraints that only apply to a subset of rows satisfying a condition:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
tracking_number VARCHAR(50),
status VARCHAR(20) NOT NULL
);
-- Add a unique constraint on tracking_number, but only for non-cancelled orders
ALTER TABLE orders
ADD CONSTRAINT unique_tracking_active_orders
UNIQUE (tracking_number)
WHERE status != 'cancelled';
This allows multiple cancelled orders to have the same tracking number (or NULL), but ensures all active orders have unique tracking numbers.
Best Practices for Unique Constraints
- Name your constraints explicitly - Use meaningful names for easier troubleshooting and maintenance.
- Consider performance implications - Unique constraints create indexes that improve lookup speed but can slow down inserts and updates.
- Plan for NULL values - Remember that multiple NULLs are allowed in unique constrained columns.
- Use appropriate column types - Ensure your column types match the data they will store to avoid unexpected constraint violations.
- Combine with NOT NULL when appropriate - If a column should never be empty and always unique, combine UNIQUE with NOT NULL.
Summary
Unique constraints are powerful tools for maintaining data integrity in PostgreSQL databases. They help you:
- Prevent duplicate values in important columns
- Automatically create indexes for better performance
- Enforce business rules at the database level
- Support single-column and multi-column uniqueness requirements
By properly implementing unique constraints, you ensure your database remains consistent and reliable, avoiding data duplication issues that could affect your application's functionality.
Additional Resources
Exercises
- Create a table for storing book information with a unique ISBN constraint.
- Modify an existing table to add a unique constraint on a combination of columns.
- Try to insert duplicate values and handle the resulting errors.
- Create a partial unique constraint that only applies to specific rows.
- Compare the performance of a table with and without unique constraints for large datasets.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)