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
