DataslopeUnique Constraints
Learn how UNIQUE constraints prevent duplicate values such as emails, usernames, and relationship pairs.
Two people can have the same name, but can two accounts use the same email address? Usually not. That is a business rule, and it belongs in the schema.
A UNIQUE constraint tells PostgreSQL that a value, or combination of values, must not repeat.
Uniqueness beyond the primary key
A primary key already guarantees one kind of uniqueness: every row has a unique identity. But many tables have other values that must be unique too.
The id identifies the row internally. The email or username may be
how humans sign in. Those are not always good primary keys, but they
still must not duplicate.
Watch a duplicate get rejected
Run this block. The first account is already present. The attempted second account reuses the same email, so PostgreSQL rejects it.
The database is not merely warning you. It refuses to store the second row because the table would no longer match the rule "one email belongs to one account."
Single-column UNIQUE
A single-column UNIQUE constraint protects one column by itself. Common examples include:
- account email
- username
- employee badge number
- product SKU
- country code
Use this when the business rule says one value identifies, names, or claims something in the real world.
Composite UNIQUE
Sometimes one column is not unique by itself, but a combination must be unique. That is a composite unique constraint.
For example, a student may enroll in many courses, and a course may hold
many students. The pair (student_id, course_id) should appear only
once, because the same student should not be enrolled in the same course
twice.
In SQL that rule looks like UNIQUE (student_id, course_id). It does
not prevent the same student from taking different courses, or different
students from taking the same course. It only prevents the same pair
from repeating.
Quick check
What does a composite UNIQUE (student_id, course_id) constraint prevent?
A student from taking more than one course.
A course from having more than one student.
The exact same student-course pair from appearing more than once.
Any NULL values in the table.
UNIQUE vs. PRIMARY KEY
A primary key and a unique constraint both prevent duplicates, but they have different jobs.
A primary key is the table's official row identity. Other tables usually point to it with foreign keys. A table has one primary key.
A unique constraint protects another business rule. A table can have
many unique constraints: one on email, one on username, another on a
relationship pair.
One-to-one relationships and natural keys
Uniqueness also shapes relationships. If profiles.user_id is both a
foreign key and unique, then each user can have at most one profile.
That is a one-to-one pattern.
Unique values can also be natural keys: real-world values that are
unique enough to identify something, such as an ISBN for a book or an
ISO country code. Even when you still use a surrogate id as the
primary key, a unique constraint can preserve the natural rule.
Unique does not always mean good primary key
An email may be unique today but still change later. A surrogate id
can remain the primary key while email UNIQUE enforces the no-duplicate
business rule.
Quick check
Why might a table have both id PRIMARY KEY and email UNIQUE?
Because a table must have two primary keys.
The id gives stable row identity, while the email rule prevents duplicate account emails.
Because UNIQUE makes the id auto-increment.
Because primary keys cannot be referenced by foreign keys.
Check your understanding
What is the main purpose of a UNIQUE constraint?
To require every column in a table to have a value.
To prevent duplicate values, or duplicate combinations of values, for a declared rule.
To connect a row to another table.
To choose a default value when one is omitted.
Which column is a common candidate for a single-column UNIQUE constraint?
first_name
created_at
email on an accounts table.
is_active
How is a UNIQUE constraint different from a PRIMARY KEY?
UNIQUE can only be used on primary key columns.
UNIQUE allows duplicates, but PRIMARY KEY does not.
A primary key is the table's official row identity; UNIQUE enforces additional no-duplicate rules.
PRIMARY KEY is only documentation, while UNIQUE is enforced.