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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
