Oh wow, I didn't know about ON CONFLICT, thanks! Unfortunately the behavior on UPDATE (actually INSERT OR REPLACE in my case) is not the desired behavior, it removes the row with the same k but different id.
However, using a TRIGGER BEFORE INSERT as well as UPDATE, as proposed by Richard, does the trick. I tried a mix of both too but the triggers were easier to understand for me. On Fri, Aug 18, 2017 at 7:08 PM David Raymond <david.raym...@tomtom.com> wrote: > Tried it and found a <potential> issue with update, though it might > actually work the way he wants. Good call though, I keep forgetting the on > conflict thing can be on table creation and not just for a query. > > --continuing from your script... > > sqlite> select * from demo; > --EQP-- 0,0,0,SCAN TABLE demo > id|k|otherstuff > 1|10|One-Mississippi > 2|40|Four-Mississippi > 3|30|Three-Mississippi > > sqlite> update demo set id = 1 where k = 30; > --EQP-- 0,0,0,SEARCH TABLE demo USING INDEX sqlite_autoindex_demo_1 (k=?) > > sqlite> select * from demo; > --EQP-- 0,0,0,SCAN TABLE demo > id|k|otherstuff > 1|30|Three-Mississippi > 2|40|Four-Mississippi > > sqlite> update demo set k = 40 where id = 1; > --EQP-- 0,0,0,SEARCH TABLE demo USING INTEGER PRIMARY KEY (rowid=?) > Error: UNIQUE constraint failed: demo.k > > sqlite> update demo set id = 1, k = 30 where id = 2; > --EQP-- 0,0,0,SEARCH TABLE demo USING INTEGER PRIMARY KEY (rowid=?) > > sqlite> select * from demo; > --EQP-- 0,0,0,SCAN TABLE demo > id|k|otherstuff > 1|30|Four-Mississippi > > sqlite> > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of R Smith > Sent: Friday, August 18, 2017 12:18 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Enforcing uniqueness from multiple indexes > > > > On 2017/08/18 6:08 PM, R Smith wrote: > > > > Isn't this what conflict clauses on constraints are for? > > > > Apologies, I usually add the test-case scripts in case anyone else wish > to test it or similar, the case in question herewith added below: > > -- SQLite version 3.17.0 [ Release: 2017-02-13 ] on SQLitespeed > version 2.0.2.4. > -- Script Items: 7 Parameter Count: 0 > -- > > ================================================================================================ > > CREATE TABLE demo( > id INTEGER PRIMARY KEY ON CONFLICT REPLACE, > k TEXT UNIQUE ON CONFLICT ABORT, > otherstuff ANY > ); > > INSERT INTO demo VALUES > (1,10,'One-Mississippi'), > (2,20,'Two-Mississippi'), > (3,30,'Three-Mississippi') > ; > > -- This one works as expected, replacing the previous key. > INSERT INTO demo VALUES (2,40,'Four-Mississippi'); > > SELECT * FROM demo; > -- id | k | otherstuff > -- ------------ | --- | ----------------- > -- 1 | 10 | One-Mississippi > -- 2 | 40 | Four-Mississippi > -- 3 | 30 | Three-Mississippi > > -- This one should fail since the id is new but k conflicts... > INSERT INTO demo VALUES (5,40,'Four-Mississippi-Again'); > -- and does: > > -- 2017-08-18 18:14:20.463 | [ERROR] UNIQUE constraint failed: > demo.k > -- Script Stats: Total Script Execution Time: 0d 00h 00m and > 00.025s > -- Total Script Query Time: 0d 00h 00m and > 00.004s > -- Total Database Rows Changed: 4 > -- Total Virtual-Machine Steps: 167 > -- Last executed Item Index: 5 > -- Last Script Error: Script Failed in Item 4: UNIQUE > constraint failed: demo.k > -- > > ------------------------------------------------------------------------------------------------ > > -- 2017-08-18 18:14:20.465 | [Info] Script failed - Rolling > back... > -- 2017-08-18 18:14:20.466 | [Success] Transaction Rolled back. > -- 2017-08-18 18:14:20.466 | [ERROR] Failed to complete: > Script Failed in Item 4: UNIQUE constraint failed: demo.k > -- > > ================================================================================================ > > _______________________________________________ > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
