Which Constraints Create Indexes?

PRIMARY KEY and UNIQUE create an index in every engine — that is how the uniqueness is enforced efficiently and how the engine finds the row. A FOREIGN KEY is the interesting case, and the answer differs by engine.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  email VARCHAR(200) NOT NULL UNIQUE,
  status VARCHAR(20) NOT NULL
);

SELECT type, name, tbl_name
FROM sqlite_master
WHERE tbl_name IN ('customers', 'orders')
  AND type = 'index'
ORDER BY name;

One row comes back: the index sqlite_autoindex_orders_1, which SQLite created for the UNIQUE constraint on email. There is no separate index for the primary key, because in SQLite an INTEGER PRIMARY KEY is an alias for the row's own rowid rather than a separate structure. And there is no index on the foreign key column customer_id — SQLite does not create one, and the name it would have had does not appear.

Here is how it compares across engines. In SQLite, the PRIMARY KEY of a non-INTEGER type and every UNIQUE constraint get an automatic index, and the rowid alias needs none. In PostgreSQL, PRIMARY KEY and UNIQUE both create a unique B-tree index, and a FOREIGN KEY creates no index on the referencing column — which surprises people, and is why adding an index on every foreign key column is a common PostgreSQL practice. In MySQL, PRIMARY KEY and UNIQUE create indexes, and a FOREIGN KEY also requires an index, which MySQL creates for you if one does not already exist. SQL Server behaves like PostgreSQL on the foreign key.

Two further points. A CHECK constraint creates no index at all — it is a predicate evaluated per row on write. And an index created for a UNIQUE constraint is a real index with all the costs of one: it has to be maintained on every insert and delete, and it takes disk space. That cost is usually worth paying, but it is a reason to be deliberate about adding unique constraints to very large tables.

SQLite keeps a catalogue of everything it has created, and querying it tells you which indexes exist and why.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (id),
  email VARCHAR(200) NOT NULL UNIQUE,
  status VARCHAR(20) NOT NULL
);

CREATE INDEX idx_orders_status ON orders (status);

SELECT name AS index_name,
       tbl_name AS table_name,
       sql AS definition
FROM sqlite_master
WHERE type = 'index'
ORDER BY tbl_name, name;

Three rows come back. Two of them have a NULL definition, because SQLite generated them automatically and does not record the SQL for an auto-created index — those are the unique indexes behind the UNIQUE constraints on customers.email and orders.email. The third, idx_orders_status, has its definition spelled out, because you created it yourself with CREATE INDEX.

The NULL definition is the tell for an auto-created index. A named index that you wrote yourself shows the statement you wrote; an automatically created index shows nothing, because the engine did not have a statement in hand when it made it.

That distinction matters when you are auditing a schema. An index named sqlite_autoindex_orders_1 came from a UNIQUE constraint, so you will find it by looking at the table definition rather than at the CREATE INDEX statements. The equivalent catalogue queries on other engines are PostgreSQL's pg_indexes and pg_index, MySQL's SHOW INDEX FROM table, and SQL Server's sys.indexes.

Notice also what is missing from the list. The customers table's primary key produces no index entry, because an INTEGER PRIMARY KEY in SQLite is the rowid itself. The foreign key on orders.customer_id produces no index, because SQLite does not create one for a foreign key — which means that if you query orders by customer_id, you need to add that index yourself. The orders table's own INTEGER PRIMARY KEY is likewise invisible, for the same rowid reason.