On 21 Dec 2017, at 3:46pm, David Raymond <david.raym...@tomtom.com> wrote:
> The only potential problem with "insert or ignore into" is that it will > ignore any constraint violation for that record insert Wait. What ? SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10)); sqlite> INSERT INTO MyTable VALUES (15); sqlite> INSERT INTO MyTable VALUES (5); Error: CHECK constraint failed: noless sqlite> INSERT OR IGNORE INTO MyTable VALUES (6); sqlite> SELECT * FROM MyTable; 15 sqlite> What the hell ? Why does that work ? Isn’t it a huge bug ? How did you discover it ? My understanding is that using INSERT OR IGNORE meant that bad inserts would fail, but they would do so silently, without triggering an error result. <https://sqlite.org/lang_conflict.html> "When an applicable constraint violation occurs, the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally." If I understand correctly, "the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong" means that a row that violates constraints will not be inserted. I thought I could enforce policy by setting constraints. Apparently not. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
