The extra space would just be the few bytes to store the text, which is nothing compared to the "extra" index size. And since it's "infrequent update" then it wouldn't be too bad for the extra time. The normal way would have an extra index to update, the trigger way would be less efficient. On an in-memory test of 10,000,000 updates, the trigger way took 14% more time, and only 2% more when I did it on disk. The "extra" unique index added 36% to the page count.
I will say that the "debugging nightmare" comment is 100% absolutely true though, and is why I'd personally bite the bullet and go with the unique keyword. I'm just pointing out how it would be done. --just testing output below this line-- SQLite version 3.17.0 2017-02-13 16:02:40 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo ...> ( ...> Id integer primary key, ...> UniqueCol int unique, ...> ExtraCol int ...> ); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> create index fooIndex on foo (UniqueCol, ExtraCol); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> explain query plan insert into foo values (1, 1, 1); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> explain insert into foo values (1, 1, 1); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 28 0 00 Start at 28 1 OpenWrite 0 2 0 3 00 root=2 iDb=0; foo 2 OpenWrite 1 4 0 k(3,,,) 00 root=4 iDb=0; fooIndex 3 OpenWrite 2 3 0 k(2,,) 00 root=3 iDb=0; sqlite_autoindex_foo_1 4 Integer 1 1 0 00 r[1]=1 5 NotNull 1 7 0 00 if r[1]!=NULL goto 7 6 NewRowid 0 1 0 00 r[1]=rowid 7 MustBeInt 1 0 0 00 8 SoftNull 2 0 0 00 r[2]=NULL 9 Integer 1 3 0 00 r[3]=1 10 Integer 1 4 0 00 r[4]=1 11 NotExists 0 13 1 00 intkey=r[1] 12 Halt 1555 2 0 foo.Id 02 13 Affinity 2 3 0 DDD 00 affinity(r[2..4]) 14 SCopy 3 6 0 00 r[6]=r[3]; UniqueCol 15 SCopy 4 7 0 00 r[7]=r[4]; ExtraCol 16 IntCopy 1 8 0 00 r[8]=r[1]; rowid 17 MakeRecord 6 3 5 00 r[5]=mkrec(r[6..8]); for fooIndex 18 SCopy 3 10 0 00 r[10]=r[3]; UniqueCol 19 IntCopy 1 11 0 00 r[11]=r[1]; rowid 20 MakeRecord 10 2 9 00 r[9]=mkrec(r[10..11]); for sqlite_autoindex_foo_1 21 NoConflict 2 23 10 1 00 key=r[10] 22 Halt 2067 2 0 foo.UniqueCol 02 23 IdxInsert 1 5 6 3 10 key=r[5] 24 IdxInsert 2 9 10 2 10 key=r[9] 25 MakeRecord 2 3 12 00 r[12]=mkrec(r[2..4]) 26 Insert 0 12 1 foo 31 intkey=r[1] data=r[12] 27 Halt 0 0 0 00 28 Transaction 0 1 2 0 01 usesStmtJournal=0 29 Goto 0 1 0 00 Run Time: real 0.031 user 0.000000 sys 0.000000 sqlite> create table bar ...> ( ...> Id integer primary key, ...> UniqueCol int, ...> ExtraCol int ...> ); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> create index barIndex on bar (UniqueCol, ExtraCol); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> create trigger bar_trg_UniqueCol_insert ...> before insert on bar ...> for each row ...> when exists (select 1 from bar where UniqueCol = new.UniqueCol) ...> begin select raise(abort, 'UNIQUE constraint failed: bar.UniqueCol'); end; Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> create trigger bar_trg_UniqueCol_update ...> before update of UniqueCol on bar ...> for each row ...> when exists (select 1 from bar where UniqueCol = new.UniqueCol) ...> begin select raise(abort, 'UNIQUE constraint failed: bar.UniqueCol'); end; Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> explain insert into bar values (1, 1, 1); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 33 0 00 Start at 33 1 OpenWrite 0 5 0 3 00 root=5 iDb=0; bar 2 OpenWrite 1 6 0 k(3,,,) 00 root=6 iDb=0; barIndex 3 Integer 1 9 0 00 r[9]=1 4 NotNull 9 6 0 00 if r[9]!=NULL goto 6 5 Integer -1 9 0 00 r[9]=-1 6 MustBeInt 9 0 0 00 7 Integer 1 10 0 00 r[10]=1 8 Copy 10 13 0 00 r[13]=r[10] 9 Integer 1 11 0 00 r[11]=1 10 Copy 11 14 0 00 r[14]=r[11] 11 Integer 1 12 0 00 r[12]=1 12 Copy 12 15 0 00 r[15]=r[12] 13 Affinity 10 3 0 DDD 00 affinity(r[10..12]) 14 Program 5 32 16 program 01 Call: bar_trg_UniqueCol_insert.default 15 Copy 13 1 0 00 r[1]=r[13] 16 NotNull 1 18 0 00 if r[1]!=NULL goto 18 17 NewRowid 0 1 0 00 r[1]=rowid 18 MustBeInt 1 0 0 00 19 SoftNull 2 0 0 00 r[2]=NULL 20 Copy 14 3 0 00 r[3]=r[14] 21 Copy 15 4 0 00 r[4]=r[15] 22 NotExists 0 24 1 00 intkey=r[1] 23 Halt 1555 2 0 bar.Id 02 24 Affinity 2 3 0 DDD 00 affinity(r[2..4]) 25 SCopy 3 6 0 00 r[6]=r[3]; UniqueCol 26 SCopy 4 7 0 00 r[7]=r[4]; ExtraCol 27 IntCopy 1 8 0 00 r[8]=r[1]; rowid 28 MakeRecord 6 3 5 00 r[5]=mkrec(r[6..8]); for barIndex 29 IdxInsert 1 5 6 3 10 key=r[5] 30 MakeRecord 2 3 17 00 r[17]=mkrec(r[2..4]) 31 Insert 0 17 1 bar 31 intkey=r[1] data=r[17] 32 Halt 0 0 0 00 33 Transaction 0 1 6 0 01 usesStmtJournal=1 34 Goto 0 1 0 00 0 Init 0 1 0 -- TRIGGER bar_trg_UniqueCol_insert 00 Start at 1; Start: bar_trg_UniqueCol_insert.default (BEFORE INSERT ON bar) 1 Once 0 13 0 00 2 Integer 0 2 0 00 r[2]=0; Init EXISTS result 3 Integer 1 3 0 00 r[3]=1; LIMIT counter 4 OpenRead 1 6 0 k(3,,,) 02 root=6 iDb=0; barIndex 5 Param 6 4 0 00 new.UniqueCol -> $4 6 IsNull 4 13 0 00 if r[4]==NULL goto 13 7 Affinity 4 1 0 D 00 affinity(r[4]) 8 SeekGE 1 13 4 1 00 key=r[4] 9 IdxGT 1 13 4 1 00 key=r[4] 10 Integer 1 2 0 00 r[2]=1 11 DecrJumpZero 3 13 0 00 if (--r[3])==0 goto 13 12 Next 1 9 0 00 13 IfNot 2 15 1 00 14 Halt 1811 2 0 UNIQUE constraint failed: bar.UniqueCol 00 15 Halt 0 0 0 00 End: bar_trg_UniqueCol_insert.default Run Time: real 0.063 user 0.000000 sys 0.015600 --saved to disk and vacuumed here sqlite> with recursive x (Id, UniqueCol, ExtraCol) as (values (1, 1, 1) union all select Id + 1, UniqueCol + 1, ExtraCol + 1 from x limit 10000000) insert into foo select * from x; --EQP-- 3,0,0,SCAN TABLE x --EQP-- 1,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) --EQP-- 0,0,0,SCAN SUBQUERY 1 Run Time: real 26.020 user 21.762140 sys 1.232408 sqlite> with recursive x (Id, UniqueCol, ExtraCol) as (values (1, 1, 1) union all select Id + 1, UniqueCol + 1, ExtraCol + 1 from x limit 10000000) insert into bar select * from x; --EQP-- 3,0,0,SCAN TABLE x --EQP-- 1,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) --EQP-- 0,0,0,SCAN SUBQUERY 1 Run Time: real 26.535 user 23.914953 sys 1.388409 sqlite> analyze; Run Time: real 3.557 user 2.652017 sys 0.904806 sqlite> vacuum; Run Time: real 41.371 user 12.043277 sys 8.408454 --ran sqlite3_analyzer *** Page counts for all tables with their indices ***************************** FOO............................................... 112646 57.7% BAR............................................... 82440 42.3% SQLITE_MASTER..................................... 1 0.0% SQLITE_STAT1...................................... 1 0.0% *** Page counts for all tables and indices separately ************************* BAR............................................... 42030 21.5% FOO............................................... 42030 21.5% BARINDEX.......................................... 40410 20.7% FOOINDEX.......................................... 40410 20.7% SQLITE_AUTOINDEX_FOO_1............................ 30206 15.5% SQLITE_MASTER..................................... 1 0.0% SQLITE_STAT1...................................... 1 0.0% -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, March 01, 2017 4:38 PM To: SQLite mailing list Subject: Re: [sqlite] Non-unique columns in unique index On 1 Mar 2017, at 9:11pm, Deon Brewis <de...@outlook.com> wrote: > "But look how much space and processing time it would take up" > > Can you clarify what you mean by "space" ? Your triggers have to be stored. Every time they’re activated (each time you insert a row) they have to be run. That requires SQLite to grab enough memory and/or disk space to run the triggers and to construct any temporary indexes they require. Also, it’s a nightmare to debug in comparison with just creating one extra index. 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
