Thanks Simon I am aware that a PK must be unique :)
It's not me that's declaring it as unique - I get to look at thousands of databases that other people create and it is these where I have noticed it (Chrome and Skype are two). I just thought it might be an area for optimisation as a redundant index is built. 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 On 19 May 2017 at 18:49, Simon Slavin <slav...@bigfraud.org> wrote: > > On 19 May 2017, at 6:21pm, Paul Sanderson <sandersonforens...@gmail.com> > wrote: > > > 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? > > There’s no point in declaring the primary key as unique. A primary key > has to be unique. SQLite will enforce uniqueness whether you tell it to or > not. > > sqlite> Create table test1(id integer primary key); > sqlite> Create table test2(id integer unique primary key); > sqlite> Create table test3(id integer primary key unique); > sqlite> PRAGMA index_list(test1); > sqlite> PRAGMA index_list(test2); > 0|sqlite_autoindex_test2_1|1|u|0 > sqlite> PRAGMA index_list(test3); > 0|sqlite_autoindex_test3_1|1|u|0 > sqlite> > > It appears that SQLite does not notice that you have declared a primary > key as unique. It’s really this that’s causing the problem. > > Simon. > _______________________________________________ > 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
