No. You asked for the extra index to be created in the table specification. It is not the job of the database engine to correct your errors (it is not even possible to know if it is an error).
If you declared that you want an extra UNIQUE index on an INTEGER PRIMARY KEY (which is already unique), then how is the software to know that you do not know what you are doing -vs- that you DO know what you are doing and need that extra UNIQUE index? Perhaps there is a reason that you want a separate unique index. Maybe the table has 15000 columns and from time to time you just need to be able to scan the used RowIDs without incurring the penalty of wafting to and fro all over the disk. So, the real optimization opportunity rests with the database designer who should not create non-required indexes. For example, one may declare: create table x(rowid unique integer primary key, value text not null collate nocase unique); create index pk_x on x as (rowid); create index fk_x on x as (value); create unique index ak_x on x (value); Although you have declared a multiplicity of redundant indexes, there is no way to tell whether you did so because (a) you intended to do so for some particular reason (ie, requirement); or, (b) you simply do not know what you are doing (ie, made an error). Computer software is supposed to do what it is told to do. Exactly. With no arguments and without interpretation of meaning. And if it does not understand, then it should spit up one of the most useful error messages ever devised: "Who?", "What?", "Where?"; and, "How?" One of the very few exceptions to this is the PL/1 Level F Optimizing Compilers. If you sputter nonsence it will try to put a "meaningful spin" on your sputterings -- one of the very first implementations of "Plug and Pray" (and, unless you really knew what you were doing, working with about the same success). -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Paul Sanderson > Sent: Friday, 19 May, 2017 11:22 > To: General Discussion of SQLite Database > Subject: [sqlite] auntondex with unique and integer primary key > > Is the autoindex associated when using unique with an integer primary key > definition redundant? > > I have seen a number of DBs/tables created in the following form: > > Create table test(id integer unique primary key); > > Insert into test values (1); > > Insert into test values (2); > > Insert into test values (3); > > The table is created and populated as expected, but an > sqlite_autoindex_test_1 is also created with content that mirrors exactly > the rowid/id. > > Is the autoindex redundant and is this an opportunity for optimisation? > > > Using 3.18.0 > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic- > Toolkit > -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
