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
