SQL: a unique index and the system catalog
Enforce uniqueness with an index and look up the index in the database's own catalog.
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';0 2 idx_users_email
Explanation
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.
Explore the dry run
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';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
| Step / line | 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 |
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.