Maybe you are confusing the autoindex logic by including superflous attributes:
... Id INTEGER NOT NULL PRIMARY KEY UNIQUE ... NOT NULL is enforced for WITHOUT ROWID tables and a single field PRIMARY KEY already implies UNIQUE, so no autoindex is required for Id This leaves only the autoindex required for the UNIQUE constraint on name. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Vincas Dargis Gesendet: Donnerstag, 14. November 2019 10:28 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Specific sqlite_autoindex_* missing in recent SQLite versions Hi list, Accidentally, when performing VACUUM using rather old SQLite 3.16.2 (from Debian 8 stretch) binary on some database file created with more recent SQLite (like 3.29.0 available in Qt 5.13.2 or a bit older), I've discovered that database now has a few more `sqlite_autodinex_*` entries in `sqlite_master` table (checked by test suite). I was puzzled at the beginning, but after some fiddling I see that creating this table: ``` CREATE TABLE "equipment_type"( id INTEGER NOT NULL PRIMARY KEY UNIQUE, name TEXT NOT NULL UNIQUE ) WITHOUT ROWID; ``` with recent SQLite, we get this set (only one) of internal indexes: ``` SELECT * from sqlite_master WHERE name LIKE 'sqlite_autoindex_eq%' index sqlite_autoindex_equipment_type_2 equipment_type 3 ``` Meanwhile, if I create same table using older 3.16.2 (on Debian 9 stretch), I get indexes for two fields: ``` index|sqlite_autoindex_equipment_type_1|equipment_type|3| index|sqlite_autoindex_equipment_type_2|equipment_type|4| ``` It feels strange if UNIQUE and PRIMARY KEY did not provoke creating `sqlite_autoindex`, but maybe we are missing something? Is this behavior change expected? Thanks! _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
