Simon, Policy is being enforced. You specifically declared in the table definition that rows must have (a > 10) in order to be "in the table". The IGNORE as in INSERT OR IGNORE means exactly and precisely what it says: INSERT the record if it is valid and IGNORE it oherwise. You tried to insert a "bad" row and specified to ignore the error causing rows so that operation was ignored and the record was not inserted.
This is working as designed and as documented. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Thursday, 21 December, 2017 10:50 >To: SQLite mailing list >Subject: [sqlite] INSERT OR IGNORE ignores constraints. Bug ? > > > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
