"Four observations that may (or may not) explain the problem: - dataset.id declared "unique": useless since the primary key is unique by definition but it may create an index (not checked). A PK is not "more unique" if an additional "unique" constraint is declared."
Declaring it as both "primary key" and "unique" makes an extra (duplicate) index, yes. With the added uniqueness checking on the duplicate index as well. SQLite version 3.29.0 2019-07-10 17:32:03 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo1 (pk integer primary key); sqlite> create table foo2(pk integer primary key unique); sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|foo1|foo1|2|CREATE TABLE foo1 (pk integer primary key) table|foo2|foo2|3|CREATE TABLE foo2(pk integer primary key unique) index|sqlite_autoindex_foo2_1|foo2|4| sqlite> explain insert into foo1 values (?); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 13 0 00 Start at 13 1 OpenWrite 0 2 0 1 00 root=2 iDb=0; foo1 2 Variable 1 1 0 00 r[1]=parameter(1,) 3 NotNull 1 5 0 00 if r[1]!=NULL goto 5 4 NewRowid 0 1 0 00 r[1]=rowid 5 MustBeInt 1 0 0 00 6 SoftNull 2 0 0 00 r[2]=NULL 7 Noop 0 0 0 00 uniqueness check for ROWID 8 NotExists 0 10 1 00 intkey=r[1] 9 Halt 1555 2 0 foo1.pk 02 10 MakeRecord 2 1 3 D 00 r[3]=mkrec(r[2]) 11 Insert 0 3 1 foo1 31 intkey=r[1] data=r[3] 12 Halt 0 0 0 00 13 Transaction 0 1 2 0 01 usesStmtJournal=0 14 Goto 0 1 0 00 sqlite> explain insert into foo2 values (?); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 22 0 00 Start at 22 1 OpenWrite 0 3 0 1 00 root=3 iDb=0; foo2 2 OpenWrite 1 4 0 k(1,) 00 root=4 iDb=0; sqlite_autoindex_foo2_1 3 Variable 1 1 0 00 r[1]=parameter(1,) 4 NotNull 1 6 0 00 if r[1]!=NULL goto 6 5 NewRowid 0 1 0 00 r[1]=rowid 6 MustBeInt 1 0 0 00 7 SoftNull 2 0 0 00 r[2]=NULL 8 Noop 0 0 0 00 uniqueness check for ROWID 9 NotExists 0 11 1 00 intkey=r[1] 10 Halt 1555 2 0 foo2.pk 02 11 Affinity 2 1 0 D 00 affinity(r[2]) 12 Noop 0 0 0 00 uniqueness check for sqlite_autoindex_foo2_1 13 SCopy 1 4 0 00 r[4]=r[1]; pk 14 IntCopy 1 5 0 00 r[5]=r[1]; rowid 15 MakeRecord 4 2 3 00 r[3]=mkrec(r[4..5]); for sqlite_autoindex_foo2_1 16 NoConflict 1 18 4 1 00 key=r[4] 17 Halt 2067 2 0 foo2.pk 02 18 MakeRecord 2 1 6 00 r[6]=mkrec(r[2]) 19 IdxInsert 1 3 4 1 10 key=r[3] 20 Insert 0 6 1 foo2 31 intkey=r[1] data=r[6] 21 Halt 0 0 0 00 22 Transaction 0 1 2 0 01 usesStmtJournal=0 23 Goto 0 1 0 00 sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
