SQL / DEFINING TABLES AND CONSTRAINTS
UNIQUE and the duplicate rows it stops
Declare UNIQUE on a column or a group of columns, predict which inserts and updates it rejects, and explain why repeated NULLs still get through.
What you will learn
- Declare UNIQUE inline on one column or as a table constraint over several
- Read a duplicate-key error and name the exact values that collided
- Pair UNIQUE with NOT NULL when repeated NULLs would defeat the rule
- Know that UNIQUE (a, b) constrains the pair, not a and b separately
Understanding UNIQUE and the duplicate rows it stops
A UNIQUE constraint is a promise that no two rows in the table agree on the value of the constrained column. The engine keeps that promise by maintaining a unique index behind the constraint and consulting it on every INSERT and every UPDATE that touches the column, so a statement that would write a second copy fails instead of writing. A table can carry as many UNIQUE constraints as the data needs, and each one can cover a different column or a different group of columns.
Duplicate means equal under the comparison rules that apply to the column, which is narrower than looking the same to a human. Under a case-sensitive collation 'Ada@Example.com' and 'ada@example.com' are two different values and both fit, while in a numeric column 1.0 and 1.00 compare equal and the second is rejected. NULL is the sharpest edge: a row is refused only when new = existing evaluates to true, and NULL = NULL evaluates to unknown, so a nullable UNIQUE column will happily hold many NULL rows.
When the constraint lists several columns the key is the whole tuple, and only a repeat of the entire combination counts as a duplicate, so UNIQUE (student_id, course_id) lets one student appear in many courses and one course hold many students while blocking the same student in the same course twice. Because the check lives in the database rather than in application code, it also survives concurrency: two sessions that each run SELECT ... WHERE email = ... and see nothing can both go on to INSERT, and the constraint is what decides that only one of them commits.
-- PostgreSQL
CREATE TABLE members (
id integer PRIMARY KEY,
email text CONSTRAINT members_email_unique UNIQUE,
nickname text NOT NULL
);
INSERT INTO members (id, email, nickname) VALUES
(1, 'ada@example.com', 'ada'),
(2, 'grace@example.com', 'grace'),
(3, NULL, 'anon1'),
(4, NULL, 'anon2');
SELECT count(*) AS rows_stored FROM members;
-- one email too many
INSERT INTO members (id, email, nickname) VALUES (5, 'ada@example.com', 'ada_again');UNIQUE rejects a row only when an existing row compares equal to it, so what counts as a duplicate depends on the column's comparison rules and, for multi-column constraints, on the whole combination of values.
Worked examples
Uniqueness over a pair of columns
Shows that a two-column UNIQUE constraint only forbids a repeat of the whole combination.
CREATE TABLE enrollments (
student_id integer NOT NULL,
course_id integer NOT NULL,
grade text,
CONSTRAINT one_seat_per_course UNIQUE (student_id, course_id)
);
INSERT INTO enrollments VALUES (7, 101, 'B'), (7, 102, 'A'), (8, 101, 'A');
INSERT INTO enrollments VALUES (7, 101, 'C');Example explained
Line 1UNIQUE (student_id, course_id) makes the pair the key, so neither column alone is restricted.
Line 2(7, 101) and (7, 102) coexist because the pairs differ, letting student 7 take two courses.
Line 3(8, 101) coexists with (7, 101) for the same reason seen from the course side.
Line 4The second (7, 101) is the first real duplicate, and DETAIL prints both colliding values.
Two UNIQUE constraints, and an UPDATE that trips one
Shows that a table can hold several UNIQUE constraints and that updates are checked just like inserts.
CREATE TABLE accounts (
id integer PRIMARY KEY,
username text NOT NULL UNIQUE,
phone text UNIQUE
);
INSERT INTO accounts VALUES (1, 'ada', '555-0100'), (2, 'grace', '555-0199');
UPDATE accounts SET username = 'ada' WHERE id = 2;Example explained
Line 1username and phone each carry their own UNIQUE constraint; the number per table is not limited to one.
Line 2The violation comes from an UPDATE, because every statement that writes the column is checked, not just inserts.
Line 3The failed UPDATE changes nothing, so row 2 still holds 'grace'.
Line 4accounts_username_key was generated by PostgreSQL as table_column_key, which is why naming constraints yourself gives more readable errors.
Case makes two values different
Shows that UNIQUE compares text with the column's collation, so capitalisation defeats a naive uniqueness rule.
CREATE TABLE logins (
email text UNIQUE
);
INSERT INTO logins VALUES ('ada@example.com'), ('Ada@Example.com');
SELECT count(*) AS stored FROM logins;
CREATE UNIQUE INDEX logins_email_ci ON logins (lower(email));Example explained
Line 1The two literals differ in capital letters, so under a case-sensitive collation they are distinct values and the constraint is satisfied.
Line 2count(*) confirms both rows were stored, which is usually not what someone declaring UNIQUE on an email column wanted.
Line 3A unique index on lower(email) expresses the case-insensitive rule instead.
Line 4Creating that index fails immediately because the rows already in the table break it.
Important notes
NULL is the one point where engines disagree: PostgreSQL, SQLite, MySQL and Oracle accept many NULLs in a UNIQUE column, SQL Server allows exactly one, and PostgreSQL 15 and later can opt in with UNIQUE NULLS NOT DISTINCT.
The check is applied row by row, so an UPDATE that shifts a whole set of values, such as SET position = position + 1, can fail partway through even though the final state would be unique; a deferrable constraint or a temporary value avoids that.
Common mistakes
Leaving a UNIQUE column nullable and then claiming the data is duplicate-free: every row that omits the value stores NULL, and any number of NULL rows are accepted.
Reading UNIQUE (customer_id, order_no) as two separate rules: customer_id may repeat freely, so the per-column uniqueness you assumed does not exist.
Guarding duplicates with a SELECT before the INSERT instead of declaring the constraint: it passes in single-user testing and lets a duplicate through as soon as two connections interleave.
Try it yourself
Change, predict, then run
Create room_bookings (room text NOT NULL, day date NOT NULL, booked_by text) with a UNIQUE constraint that lets a room be booked on many days and a day hold many rooms, but never the same room twice on the same day. Insert three rows that must succeed and one that must fail, and check that the error names both colliding values.
Open the SQL workspaceCheck your understanding
A table declares UNIQUE (project_id, task_name) and already holds (1, 'build') and (2, 'build'). Why was the second row accepted?
- The constraint compares the two columns as one key, and (2, 'build') differs from (1, 'build') in project_id
- task_name is only checked for rows whose project_id already appeared
- A multi-column UNIQUE constraint is enforced only on the first column listed
- The database keeps one unique index per column, and each column still has distinct values somewhere
Show answer
A multi-column UNIQUE constraint has a single key made of the whole combination, and two keys are duplicates only when every column compares equal, so a repeated task_name under a different project_id is a different key. The last option is tempting because it sounds like the constraint expands into per-column rules, but one constraint creates one index over the tuple; if project_id and task_name each had to be unique on their own you would have to declare two separate UNIQUE constraints, and then (2, 'build') would have been rejected.