on conflict depends on conflict

(1) By Roman (moskvich412) on 2020-12-28 01:34:46 [link] [source]

Dear SQLiters,

I think I am asking something strange... If there is a table with several UNIQUE (or other constrains) is it possible to specify what happens on each individual conflict separately? Example:

CREATE TABLE t(a, b, UNIQUE(a), UNIQUE(b));

INSERT INTO t(a,b) VALUES (1,2);

INSERT OR IGNORE INTO t(a,b) VALUES (1,1); -- nothing inserted, no error

INSERT OR IGNORE INTO t(a,b) VALUES (1,2); -- nothing inserted, no error

I would like:

INSERT OR IGNORE a FAIL ON b INTO t(a,b) VALUES (1,1); -- nothing inserted, no error

INSERT OR IGNORE a FAIL ON b INTO t(a,b) VALUES (1,2); -- nothing inserted, FAIL

I would like to be able to specify which conflict to ignore and which should terminate insert. I understand that this can be handled by proper VALUE() clause.

Thank you,

Roman

(2.1) By Keith Medcalf (kmedcalf) on 2020-12-28 02:53:17 edited from 2.0 in reply to 1 [source]

This is using your same base table as in:

CREATE TABLE t
(
    a UNIQUE, 
    b UNIQUE
);
INSERT INTO t(a,b) VALUES (1,2);

This statement will be ignored (DO NOTHING). See https://sqlite.org/lang_UPSERT.html. This is apparently called colloquially an "upsert".

INSERT or FAIL INTO t(a,b) VALUES (1,1) ON CONFLICT (a) DO NOTHING;

Of course, in your example attempting to insert (1,2) does not fail because the constraint on a being unique is is handled by DO NOTHING, at which point there is no longer any conflict to be resolved.

INSERT or FAIL INTO t(a,b) VALUES (2,2) ON CONFLICT (a) DO NOTHING;

In this case the unique constraint on b is violated for which there is no specific conflict resolution method so the conflict "percolates" up to the statement level conflict resolution method.

Unfortunately there is no DO clause which allows you to trigger a standard conflict resolution method. If DO could trigger the standard resolution methods including ABORT FAIL ROLLBACK IGNORE (which is already there as NOTHING) then the following would work -- assuming that multiple ON CONFLICT clauses were processed in-order left to right:

INSERT INTO t(a,b) VALUES (1,2) ON CONFLICT (b) DO FAIL ON CONFLICT (a) DO NOTHING;

(3) By Keith Medcalf (kmedcalf) on 2020-12-28 03:10:55 in reply to 1 [link] [source]

Note that the changes which allow multiple ON CONFLICT clauses, for them to be executed in-order, and for a generic ON CONFLICT DO tail is part of the next release of SQLite3 https://sqlite.org/draft/releaselog/3_35_0.html currently scheduled for the end of March 2021.

Perhaps Richard will evaluate whether it is feasible to add the regular conflict resolution methods so that you can DO ABORT / DO FAIL / DO IGNORE / DO ROLLBACK in addition to the current DO UPDATE and DO NOTHING for the same release.