SQL: a unique index and the system catalog

Before you start

SQL CREATE TABLE and INSERT.

Enforce uniqueness with an index and look up the index in the database's own catalog.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

An index is an extra structure, usually a B-tree, that lets the database find rows by a column value without scanning the whole table. Creating a UNIQUE index also makes the database reject a second row with the same value. The index speeds up reads and costs extra work and space on every insert and update.

Databases describe themselves in catalog tables. In SQLite the sqlite_master table lists every table, index, view, and trigger. In the example, the unique index on email blocks a second user with the same address. INSERT OR IGNORE skips that row, and changes() reports how many rows the last statement changed.

Code example

SQL

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
INSERT INTO users VALUES (1, 'a@x.com', 'Ada'), (2, 'b@x.com', 'Bo');

CREATE UNIQUE INDEX idx_users_email ON users(email);
INSERT OR IGNORE INTO users VALUES (3, 'a@x.com', 'Impostor');

SELECT changes();
SELECT COUNT(*) FROM users;
SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'users';

Output

0
2
idx_users_email

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);

2INSERT INTO users VALUES (1, 'a@x.com', 'Ada'), (2, 'b@x.com', 'Bo');

4CREATE UNIQUE INDEX idx_users_email ON users(email);

5INSERT OR IGNORE INTO users VALUES (3, 'a@x.com', 'Impostor');

7SELECT changes();

8SELECT COUNT(*) FROM users;

9SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'users';

Follow the example

Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.

View the complete dry-run table

Prepared trace of the published program. Some rows group repeated iterations.
What happens	State after step	Output so far
Build an ordered index on email and forbid duplicate values.	`—`	—
The address a@x.com is already in the index, so this row is skipped.	`—`	—
The last statement changed 0 rows.	`—`	0
Still only the original two users.	`—`	0 2
The catalog lists the index we created.	`—`	0 2 idx_users_email

Behind the scenes

How SQL processes this code

The database parses and plans the query, accesses the table, and returns the selected rows. Physical execution order may differ from the query’s written order.

Cost and common mistakes

Lookup in the index is O(log n); each insert pays an extra O(log n) to maintain it.

Indexing every column slows down writes and wastes space. Index the columns that queries filter, join, and sort on.
A unique index treats NULLs as distinct in SQLite and most databases, so many rows can have a NULL email.
Without OR IGNORE, the duplicate insert would stop the script with a constraint error.

Try it yourself

What would changes() return if the insert had used a new email address?

Reveal answer and reasoning

1, because one row would have been inserted.