Data Integrity and Constraints

A uniqueness constraint ensures all values in a column are unique by using a special index to detect and block duplicates.

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 an email address for every user. If the same email address is stored for two different users, the database may no longer represent the rule that each user should have a distinct email. A uniqueness constraint protects this rule at the database level. It ensures that values in a constrained column are unique and blocks an insert or update when the value already exists.

What happens when a new row contains a value that already exists in the constrained column?

What do you think happens?

A unique index already contains alex@example.com. What should happen when another row tries to store alex@example.com?

The new row is accepted and the index stores the value twice
The operation is rejected and the transaction is rolled back
The existing row is automatically deleted
The database ignores the uniqueness rule for inserts

Reveal answer

Answer:

The operation is rejected and the transaction is rolled back

When a duplicate is detected, the database stops the operation before it completes, rolls back the transaction, and returns an error indicating that the uniqueness constraint was violated.

The Unique Index as Gatekeeper

A uniqueness constraint is enforced through a special index. When the database creates a unique index on a column, the index keeps track of every value in that column and the locations of the rows containing those values. The index maintains an ordered or hashed collection of unique values and their row locations.

During an insert or update, the database checks the unique index before allowing the change. If the incoming value is already present, the constraint is violated and the operation is blocked. If the value is not present, the operation proceeds and the index is updated to include the new value. The source describes this lookup as occurring in near-constant time, so the index can act as an immediate gatekeeper rather than waiting until after the row has been stored.

How does the database use an index to check whether a value already exists before accepting an insert or update?

Declaring Uniqueness with CREATE INDEX

Definition

The UNIQUE keyword in a CREATE INDEX statement tells the database to enforce uniqueness for the indexed column. Including UNIQUE transforms a regular index into a constraint-enforcing index. Without UNIQUE, an index can speed up queries but does not prevent duplicate values.

sql

CREATE UNIQUE INDEX unique_user_email
ON users (email);

CREATE UNIQUE INDEX unique_user_email
ON users (email);

Which part of a CREATE INDEX statement makes the index enforce uniqueness, and how does the statement map to the resulting database structure?

Worked example

Protecting User Email Addresses

A users table should not contain two rows with the same email address. Decide what database rule is needed and identify the result of a duplicate insert.

1
Choose the protected column
The email column is the value that must not be duplicated because the business requirement is that each user should have a unique email address.
2
Create a unique index
Use CREATE INDEX with the UNIQUE keyword for the users email column. The UNIQUE keyword makes the index enforce the no-duplicates rule.
3
Check a new operation
Before an insert or update completes, the database checks the incoming email against the values tracked by the unique index.
4
Handle a duplicate
If the email is already present, the database rejects the operation, rolls back the transaction, and returns an error.

Result

The table remains unchanged after the attempted duplicate operation, so the uniqueness rule protects the data even if an application sends an invalid duplicate.

Choosing Columns for Uniqueness

Use a uniqueness constraint when the business logic requires that a column have no duplicate values. The rule should describe a real requirement, not merely a preference for distinct-looking data. Examples from the source include email addresses in a users table, usernames, ISBN numbers in a books table, and product codes in an inventory system.

Which values should be unique, and what is the difference between valid unique data and data where duplicates are allowed?

Database design choice	Meaning	Effect on duplicate values
Unique constraint	A specific column must contain no duplicate values	Duplicate insert or update is rejected
Regular index	An index is created without the uniqueness rule	Duplicates are not prevented by the index
Primary key	The table's main identifier	The primary key is unique, and a table can have only one primary key

A unique constraint, a regular index, and a primary key serve different design purposes.

Mistakes That Weaken Data Integrity

Common mistake
Creating a regular index when the requirement is uniqueness
What it looks like
Creating an index on email without the UNIQUE keyword
Why it fails
A regular index can speed up queries, but it does not prevent duplicate values.
Do this instead
Include UNIQUE in the CREATE INDEX statement when the column must contain no duplicates.
Common mistake
Assuming the application alone can protect uniqueness
What it looks like
Checking whether an email exists in application code but not enforcing a unique index
Why it fails
The database-level constraint is the safety net that catches data entry errors or application bugs that might otherwise allow duplicates.
Do this instead
Enforce the requirement through a unique index so insert and update operations are checked by the database.
Common mistake
Treating every unique column as the primary key
What it looks like
Assuming a unique email column replaces the table's main identifier
Why it fails
A table can have multiple unique constraints, but only one primary key. A primary key is the table's main identifier and is used to reference rows from other tables.
Do this instead
Keep the primary-key role separate from additional uniqueness rules based on business requirements.
Common mistake
Expecting a rejected duplicate operation to partially remain in the table
What it looks like
Assuming the database stores the row and merely reports a warning
Why it fails
When a uniqueness violation occurs, the operation is stopped, the transaction is rolled back, and an error is returned.
Do this instead
Handle the returned error in the application and understand that the violating change was not completed.

Practice the Gatekeeper Process

MEDIUM

A books table stores ISBN numbers. The database design requires that each ISBN identify only one book. Explain why a unique index is appropriate, identify what the database checks before an insert or update, and describe what happens if the incoming ISBN is already present.

Hints

Start with the business requirement: the ISBN must not be duplicated.
The database checks the value against the unique index before allowing the operation.
A duplicate causes rejection, rollback, and an error.

Your attempt (not saved)

Worked example

Checking the ISBN Rule

A new book is submitted with an ISBN that is already tracked by the unique index.

1
Identify the rule
ISBN numbers are an example of values that may need to be unique in a books table.
2
Perform the index check
The database looks for the incoming ISBN in the unique index before completing the insert or update.
3
Reject the duplicate
Because the value is already present, the database blocks the operation.
4
Protect the table
The transaction is rolled back and an error is returned, so the duplicate value is not stored.

Result

The unique index prevents the books table from containing two rows with the same ISBN.

Key Takeaways

1
A uniqueness constraint ensures that values in a constrained column are not duplicated.
2
The database enforces the rule through a special unique index that tracks values and their row locations.
3
The UNIQUE keyword in CREATE INDEX changes a regular index into a constraint-enforcing index.
4
Before an insert or update completes, the database checks the unique index; a duplicate causes rejection, rollback, and an error.
5
Unique constraints protect business requirements such as distinct email addresses, usernames, ISBN numbers, and product codes, while remaining distinct from the table's single primary key.

At a glance

Uniqueness constraints prevent duplicate values in columns where the business rule requires distinct data.
A unique index checks incoming values during insert and update operations before the database accepts the change.
The UNIQUE keyword in a CREATE INDEX statement is what makes the index enforce uniqueness.
A duplicate causes the operation to be rejected, the transaction to be rolled back, and an error to be returned.
A table may have multiple unique constraints but only one primary key.

Related concepts

More concepts starting with D