SQL: a unique index and the system catalog
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.
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';