CHECK and ON CONFLICT

(1) By anonymous on 2021-07-13 00:13:57 [link] [source]

In https://www.sqlite.org/lang_conflict.html it says:

The ON CONFLICT clause applies to [...] CHECK constraints.

However on the diagram table-constraint https://www.sqlite.org/lang_createtable.html#syntax the CHECK constraint is not followed by a conflict-clause.

Check test case:

sqlite> create table test(id int check(id > 0) on conflict ignore);

Error: near "on": syntax error

Primary key test case:

sqlite> create table test(id int PRIMARY KEY on conflict ignore);

sqlite> insert into test(id) values (0);

sqlite> select * from test;

0

Am I misunderstanding the documentation, or is the documentation wrong?

Thank you in advance for your help.

David

(2) By Keith Medcalf (kmedcalf) on 2021-07-13 00:38:06 in reply to 1 [source]

That ON CONFLICT clause is referring to the statement handling of internal conflicts. That is:

INSERT ON CONFLICT IGNORE INTO

...

which reduces to

INSERT OR IGNORE INTO

...

This statement level ON CONFLICT clause catches CHECK constraints and handles them in the requested fashion.

Your example is using more specific ON CONFLICT clause. There is no specific handler for the CHECK constraint, it is always FAIL and it is presented to the statement level handler.

(3) By anonymous on 2021-07-13 04:20:26 in reply to 2 [link] [source]

I ended up pre-processing my data with Python.

Thank you Keith, I really appreciate you taking the time to answer my question.

David