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:
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:
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:
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:
Important Notes:
1. Unique vs. Primary Key
2. Handling Unique Violations
3. Unique Constraint and NULL Values
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4