SQLite Forum

Feature Request: Create *unique* index that's also "covering" extra columns
Login

Feature Request: Create *unique* index that's also "covering" extra columns

(1.2) By ddevienne on 2020-04-23 09:31:01 edited from 1.1 [source]

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 it
seems I was wrong.

Therefore, it appears to be impossible to create a UNIQUE covering index,
the way it's possible in PostgreSQL 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.