Interview MasterSQL Learning
SQL UNIQUE Constraint
SQL Learning
SQL UNIQUE Constraint
Use the UNIQUE constraint to prevent duplicate values in columns and protect data integrity in your database.
What Is the SQL UNIQUE Constraint?
The UNIQUE constraint ensures that all values in a column—or a combination of columns—are different from each other. It's like a PRIMARY KEY, except a table can have multiple UNIQUE constraints and the column can contain NULL (depending on the SQL dialect).
Basic UNIQUE Constraint Syntax
Inline Definition
CREATE TABLE Users (
user_id INTEGER PRIMARY KEY,
username TEXT UNIQUE,
email TEXT UNIQUE NOT NULL
);Table-Level Definition
CREATE TABLE Users (
user_id INTEGER PRIMARY KEY,
username TEXT,
email TEXT NOT NULL,
UNIQUE (username),
UNIQUE (email)
);Composite UNIQUE Constraint
You can enforce uniqueness across multiple columns. For example, no user can leave more than one review per video:
CREATE TABLE Reviews (
review_id INTEGER PRIMARY KEY,
user_id INTEGER,
video_id INTEGER,
rating INTEGER,
UNIQUE (user_id, video_id)
);Adding UNIQUE with ALTER TABLE
ALTER TABLE Users
ADD CONSTRAINT unique_username UNIQUE (username);Difference Between UNIQUE and PRIMARY KEY
- Both enforce uniqueness
PRIMARY KEYalso impliesNOT NULL- Only one
PRIMARY KEYper table, but manyUNIQUEconstraints are allowed
Best Practices for UNIQUE
- Use it for usernames, emails, or natural keys that should never repeat
- Use a
PRIMARY KEYfor row identity,UNIQUEfor validation rules - Index your UNIQUE columns for faster lookups and enforcement
- Be aware: some SQL engines treat
NULLvalues as unique; others don't