SQLite can create non-UNIQUE covering indexes, w/o any additional syntax, but I thought there was also explicit syntax for that, but looking at [the doc][1] it seems I was wrong. Therefore, it appears to be impossible to create a **UNIQUE** covering index, the way it's possible in [PostgreSQL][2] using the following syntax: ``` CREATE [UNIQUE] INDEX ... ON tab(cols...) INCLUDE(extra_cols...) ``` Any chance this can be added to SQLite? I suspect all the internal machinery exists, with existing non-unique covering indexes, and without-rowid tables, the only difference is that the uniqueness is enforced on a prefix of the index's column(s) only. This would bring parity between SQLite and PostgreSQL, always a good thing, and allow to optimize queries to avoid index **and** table IO. Which is exactly what I was trying to do, and discovered I couldn't in this case. My $0.02. Here's my exact use case, FWIW: ``` CREATE TABLE enum ( cat int NOT NULL, -- category / kind idx int NOT NULL, -- enum numeric ID txt text NOT NULL, -- enum textual ID / name [other columns...], -- additional "payload" columns PRIMARY KEY (cat, idx) ) WITHOUT ROWID; CREATE UNIQUE INDEX enum_by_txt ON enum(cat, txt) INCLUDE(idx); ``` I.e. equally fast *single-IO* access by (cat, idx) or (cat, txt). The `enum` table and its index are mostly read-only, with writes being append-only. Other tables will have FKs to (cat, idx), while outside queries will join on those tables via the `enum.idx` table but bind *textual IDs*, thus need `txt` to `idx` translation. [1]: https://www.sqlite.org/lang_createindex.html [2]: https://www.postgresql.org/docs/12/indexes-index-only-scans.html