NOT NULL controls whether a value exists. UNIQUE controls whether it's allowed to repeat, every value in a UNIQUE column has to be different from every other row's value in that same column.
Learning Objectives
- Declare a single-column and a composite (multi-column) UNIQUE constraint.
- State precisely how UNIQUE differs from PRIMARY KEY.
- Add UNIQUE to an existing table, including SQLite's different approach.
A Single-Column UNIQUE Constraint
Once this runs, no two employees rows can share the same email value, an INSERT or UPDATE that would create a duplicate is rejected. This assumes employees already has an email column, added back in the alter-table lesson in Module 7.
ALTER TABLE employees ADD CONSTRAINT uq_employees_email UNIQUE (email);
A Composite UNIQUE Constraint
This is a composite constraint, employee_id and project_id are checked together, not separately. The same employee_id can appear in many rows (assigned to several projects), and the same project_id can appear in many rows (many employees on one project), but that specific pairing can only appear once, preventing the same employee from being assigned to the same project twice.
ALTER TABLE project_assignments ADD CONSTRAINT uq_one_assignment UNIQUE (employee_id, project_id);
UNIQUE vs PRIMARY KEY
| UNIQUE | PRIMARY KEY | |
|---|---|---|
| How many per table | As many as needed | Exactly one |
| Allows NULL? | Yes, on most databases (see note below) | No, implies NOT NULL automatically |
| Purpose | Prevents duplicate values in a column that isn't the row's identity | Identifies each row uniquely |
| Beacon Robotics example | employees.email, no two employees share one | employees.id, the row's actual identity |
A genuine, commonly-tested quirk: standard SQL treats NULL as never equal to another NULL, so most databases allow multiple NULLs in a UNIQUE column, each one is considered distinct from the others. SQL Server is the exception, its UNIQUE constraint is implemented as an index that treats NULL as a comparable value, so it allows only one NULL per UNIQUE column, a real, interview-relevant difference from the other five.
Adding UNIQUE: Where SQLite Diverges
SQLite's ALTER TABLE doesn't support ADD CONSTRAINT at all, there's no way to add a named UNIQUE constraint to an existing table directly. The practical equivalent is a UNIQUE index instead, which enforces the identical rule.
CREATE UNIQUE INDEX idx_employees_email ON employees(email);
Try It Yourself
Write a composite UNIQUE constraint on project_assignments(employee_id, project_id), named uq_assignment, on PostgreSQL.
ALTER TABLE project_assignments ADD CONSTRAINT uq_assignment UNIQUE (employee_id, project_id);
Frequently Asked Questions
Does a UNIQUE constraint create an index automatically?
Yes, on every database in this course, enforcing uniqueness requires a fast way to check for existing duplicates, so the database builds an index behind the scenes automatically. This is also why UNIQUE columns are fast to filter on, the same index that enforces the rule speeds up lookups.
Can two different UNIQUE constraints exist on the same table?
Yes, freely, a users table could have separate UNIQUE constraints on email and on username, each enforced independently. There's no limit like PRIMARY KEY's one-per-table rule.
Common Beginner Mistakes
Expecting a composite UNIQUE constraint to block each column individually
It checks the combination, not each column alone. UNIQUE (employee_id, project_id) allows employee_id 3 to repeat across many rows, and project_id 1 to repeat across many rows, it only blocks the exact pair (3, 1) from appearing twice.
Trying ALTER TABLE ... ADD CONSTRAINT on SQLite
SQLite's ALTER TABLE doesn't support adding named constraints at all, this syntax simply isn't recognized. CREATE UNIQUE INDEX is the real equivalent there, not a named constraint.
Best Practices
- Add UNIQUE to any column meant to be a real-world identifier, like email or a product SKU, even when it isn't the primary key.
- Use a composite UNIQUE constraint for junction tables like project_assignments, to block the exact same pairing being inserted twice.
- Name UNIQUE constraints explicitly (uq_ prefix), the same reasoning as every other constraint type in this module.
Interview Questions
What's the real difference between UNIQUE and PRIMARY KEY, beyond "PRIMARY KEY is special"?
A table can have many UNIQUE constraints but only one PRIMARY KEY. PRIMARY KEY always implies NOT NULL, UNIQUE generally still allows NULL (SQL Server being the one exception, which allows only a single NULL per UNIQUE column, since its implementation treats NULL as a comparable index value). PRIMARY KEY represents the row's actual identity, UNIQUE just prevents duplicates in a column that isn't necessarily that identity.
Write a UNIQUE constraint ensuring no two employees share the same email, on MySQL, with an explicit name.
ALTER TABLE employees ADD CONSTRAINT uq_employees_email UNIQUE (email); — naming it uq_employees_email makes a later DROP INDEX uq_employees_email ON employees; (MySQL's DROP syntax for a UNIQUE constraint) unambiguous.
Summary
UNIQUE guarantees every value in a column, or every combination of values across several columns, is distinct across the table. It differs from PRIMARY KEY by allowing multiple instances per table and (mostly) allowing NULL, SQL Server's single-NULL-only behavior being the real exception worth remembering.
What's Next?
PRIMARY KEY is next, the constraint that combines NOT NULL and UNIQUE into a table's actual identity, including the composite primary key project_assignments has been waiting on since Module 7.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.