Primary Keys: Identifying Rows Uniquely

The index does more than speed up a query in this situation. With uniqueness enforcement enabled, it becomes a gatekeeper: it checks every relevant insert and update and prevents a value from appearing more than once.

Programming

Interactive lab

Try it: Keys and Constraints

How SQLite enforces PRIMARY KEY, UNIQUE, NOT NULL and FOREIGN KEY constraints on INSERT and DELETE, how an INTEGER PRIMARY KEY assigns ids (with and without AUTOINCREMENT), what INSERT OR IGNORE skips, and what ON DELETE RESTRICT, CASCADE and SET NULL do to child rows.

How it works

INSERT: a NULL id is replaced by max(id) + 1 — or, with AUTOINCREMENT, by one more than the largest id ever used — and last_insert_rowid() reports it.
Constraints are checked in order: NOT NULL, then the PRIMARY KEY, then UNIQUE, then the FOREIGN KEY (the parent row must exist). The first failure aborts the statement with SQLite's error message.
INSERT OR IGNORE silently skips a row that breaks NOT NULL, PRIMARY KEY or UNIQUE — but a foreign-key failure is still an error.
DELETE of a parent row that has children: RESTRICT refuses, CASCADE deletes the children too, SET NULL clears their foreign key. None of this happens unless PRAGMA foreign_keys = ON (SQLite's default is OFF).

Default run (5 steps): 3 artists and 3 albums. PRAGMA foreign_keys = ON, ON DELETE RESTRICT. Choose an operation. Example: INSERT INTO artist (name) VALUES ('Queen'). … Error: UNIQUE constraint failed: artist.name. Nothing was changed (the statement is rolled back).

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Two fixed tables of at most 8 rows and ids up to 99; changing the schema recreates the tables with the starting rows. Error texts and ids were checked step by step against sqlite3.

Educational simulation

Loading the simulation…

The Duplicate-Value Problem

Suppose a users table stores email addresses. If two rows contain the same email address, an application may no longer know which row represents that user. A uniqueness constraint prevents this situation by requiring every value in the constrained column to be unique. The database checks the rule itself, so duplicate values are blocked even when they result from an application mistake.

What do you think happens?

A users table already contains the email value alex@example.com. What happens when another row tries to use that same value in a uniquely constrained email column?

The database accepts both rows
The database replaces the older row
The database rejects the operation
The database removes the unique constraint

Reveal answer

Answer:

The database rejects the operation

The database checks the unique index before completing the insert or update. If the value already exists, the operation is blocked, the transaction is rolled back, and an error is returned.

What happens when an insert tries to add a value that already exists in a uniquely constrained column?

The Index as Gatekeeper

A uniqueness constraint is enforced through a special index. The index keeps track of the values in the constrained column and the row locations associated with those values. When an insert or update introduces a value, the database checks the index before allowing the change. If the value is already present, the constraint is violated. If the value is not present, the operation proceeds and the index is updated with the new value.

How does the database use an index to find an existing value and decide whether a new row is allowed?

Key point

Following Values to Rows

Think of the unique index as a collection of unique column values paired with the locations of their rows. For example, an index on email can associate alex@example.com with the row for Alex and sam@example.com with the row for Sam. If a new row supplies alex@example.com, the database finds that value in the index and knows that the value is already associated with a row. The duplicate is therefore rejected.

How do the values stored in a unique index correspond to the rows they identify in the table?

Adding UNIQUE to an Index

The UNIQUE keyword is optional in a CREATE INDEX statement. Without UNIQUE, the index helps speed up queries but does not prevent duplicate values. Including UNIQUE changes the index into a constraint-enforcing index. The database then applies the uniqueness rule automatically to future insert and update operations involving that column.

Where does the UNIQUE keyword appear in a CREATE INDEX statement, and what changes when it is included?

Best practice

When a column must never contain duplicates because of a business rule, use a uniqueness constraint rather than relying only on application checks. The database then protects the rule for every insert and update operation that affects the column.

Primary Key or Unique Column

A primary key and a uniqueness constraint both involve uniqueness, but they are not the same role. A primary key is the table's main identifier and is used to reference rows from other tables. A table can have only one primary key. A table can have multiple unique constraints that protect additional columns from duplicates.

Database feature	Purpose	How many can a table have?	Source example
Primary key	Main identifier for the table's rows and a value used to reference rows from other tables	One	user_id
Uniqueness constraint	Prevents duplicate values in a column because of a business requirement	Multiple	email

Both protect uniqueness, but a primary key identifies the table's rows while a uniqueness constraint protects an additional rule.

Choosing Columns That Must Be Unique

Use a uniqueness constraint whenever the business logic requires that a column contain no duplicate values. The source examples include email addresses in a users table, usernames, ISBN numbers in a books table, and product codes in an inventory system. In each case, the rule protects data integrity by catching data-entry errors or application bugs that might otherwise create duplicates.

Worked example

Protecting User Email Addresses

A users table has user_id as its main identifier. The application requires every user to have a different email address.

1
Identify the business rule
The email column must not contain duplicate values, so it needs a uniqueness constraint.
2
Keep the primary key role separate
user_id remains the table's main identifier, while email receives a separate uniqueness rule.
3
Let the database enforce the rule
A unique index on email checks future inserts and updates. If an email already exists, the attempted change is rejected and the transaction is rolled back.

Result

The table can have one primary key on user_id and an additional uniqueness constraint on email.

Common Design Mistakes

Common mistake
Assuming that every regular index prevents duplicates.
What it looks like
A column has an index, but repeated values are still inserted.
Why it fails
The UNIQUE keyword is what transforms a regular index into a constraint-enforcing index.
Do this instead
Use the UNIQUE keyword when the column must contain no duplicate values.
Common mistake
Treating every unique column as the primary key.
What it looks like
An email column is unique, so it is assumed to be the table's primary key.
Why it fails
A primary key is the table's main identifier, while a table can have multiple additional unique constraints.
Do this instead
Choose the primary key for the table's main row-identity role and use uniqueness constraints for separate business rules.
Common mistake
Relying only on application validation to detect duplicates.
What it looks like
The application checks whether an email is available, but another operation inserts the same email before the first operation finishes.
Why it fails
The database itself must enforce the rule to protect data integrity during inserts and updates.
Do this instead
Create a unique index so the database checks the value before allowing the change.
Common mistake
Ignoring the result of a rejected operation.
What it looks like
An insert fails because a duplicate value was detected, but the application continues as if the row was stored.
Why it fails
The database returns an error and rolls back the transaction, so the attempted change was not made.
Do this instead
Handle the database error and treat the operation as unsuccessful.

Check Your Understanding

MEDIUM

A books table stores ISBN values. The same ISBN must not appear in two rows, but the table already has a different primary key. Explain which database rule should be added, what the unique index should check, and what should happen if an insert repeats an existing ISBN.

Hints

The rule is based on preventing duplicate values in one column.
A table may have multiple uniqueness constraints but only one primary key.
The database checks the index before completing the insert.

Your attempt (not saved)

1
A uniqueness constraint requires every value in a column to be different. A unique index stores the column's values and row locations and checks new inserts or updates before they complete. The UNIQUE keyword makes a CREATE INDEX statement enforce this rule; without it, the index does not prevent duplicates. A uniqueness constraint is separate from a primary key, so a table can use one primary key and several additional unique constraints. Apply the rule whenever business logic requires values such as emails, usernames, ISBNs, or product codes to remain unique.

Key Takeaways

A uniqueness constraint prevents duplicate values in a column.
The database enforces the rule through a unique index that checks inserts and updates.
The UNIQUE keyword changes a regular index into a constraint-enforcing index.
A primary key is the table's main identifier, while additional unique constraints protect other business rules.
Use uniqueness constraints for values such as emails, usernames, ISBNs, and product codes when duplicates would damage data integrity.

Related concepts

More concepts starting with P