Check an index definition with PRAGMA index_info
Also published in our primary archive.
Check an index definition with PRAGMA index_info
To check an SQLite index definition, use PRAGMA index_info(idx_pair) for its ordered key columns. For CREATE INDEX idx_pair ON t(a, b), the required rows are sequence 0:a then 1:b. An index name and a normal query result cannot establish that order.
This fixture also covers a boundary that column inspection alone misses. Its failing input creates idx_pair on the correct columns but makes it partial with WHERE b IS NOT NULL. The table schema, seed rows, and naïve SELECT all look ordinary. PRAGMA index_info also reports 0:a,1:b, so it passes the column-order portion of the check. PRAGMA index_list(t) supplies the separate partial flag; the combined expected-definition check is false and prints the observed failure. This is a meaningful migration check because an index can have the desired key sequence while still covering only some rows.
The correction drops that index and creates exactly CREATE INDEX idx_pair ON t(a, b). The final assertions require both ordered index_info rows and a non-partial entry from index_list. These assertions establish metadata in this synthetic SQLite database; they do not prove a query plan, performance, or behavior in another database engine. PRAGMAs are SQLite-specific, and SQLite notes that individual pragma behavior can change between releases (SQLite PRAGMA documentation). Python's standard-library sqlite3.Connection.execute runs the SQL and returns cursors used by fetchall (Python sqlite3 documentation). No newer Python-only API is used; use a supported Python 3 build that includes sqlite3.
AI assistance disclosure: Batu Lab Notes used AI assistance to draft this reproducible synthetic example.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(a TEXT, b TEXT)")
con.executemany("INSERT INTO t VALUES (?, ?)", [("x", "one"), ("y", None)])
print(con.execute("SELECT sql FROM sqlite_master WHERE name = 't'").fetchone()[0])
print("seed=" + repr(con.execute("SELECT a, b FROM t ORDER BY a").fetchall()))
print("naive SELECT=" + repr(con.execute("SELECT a FROM t WHERE b IS NOT NULL").fetchall()))
con.execute("CREATE INDEX idx_pair ON t(a, b) WHERE b IS NOT NULL")
wrong_columns = [(seq, name) for seq, _cid, name in con.execute("PRAGMA index_info(idx_pair)")]
wrong_partial = next(row[4] for row in con.execute("PRAGMA index_list(t)") if row[1] == "idx_pair")
print("failing index_info=" + ",".join(f"{seq}:{name}" for seq, name in wrong_columns))
print(f"failing partial={wrong_partial}")
assert not (wrong_columns == [(0, "a"), (1, "b")] and wrong_partial == 0)
con.execute("DROP INDEX idx_pair")
con.execute("CREATE INDEX idx_pair ON t(a, b)")
correct_columns = [(seq, name) for seq, _cid, name in con.execute("PRAGMA index_info(idx_pair)")]
correct_partial = next(row[4] for row in con.execute("PRAGMA index_list(t)") if row[1] == "idx_pair")
assert correct_columns == [(0, "a"), (1, "b")]
assert correct_partial == 0
print("corrected index_info=" + ",".join(f"{seq}:{name}" for seq, name in correct_columns))
print(f"corrected partial={correct_partial}")CREATE TABLE t(a TEXT, b TEXT)
seed=[('x', 'one'), ('y', None)]
naive SELECT=[('x',)]
failing index_info=0:a,1:b
failing partial=1
corrected index_info=0:a,1:b
corrected partial=0