On 1 Dec 2015, at 12:55am, Yuri <yuri at rawbw.com> wrote: > On 11/23/2015 07:55, James K. Lowden wrote: >> As a matter of fact, violation of UNIQUE & PK constraints is enforced >> rather too strictly in SQLite. Any UPDATE statement that modifie more >> than one row of a UNIQUE column can fail because two rows may >> *transiently* have the same value. SQLite will fail the statement >> even though the completed transactation leaves the constraint >> unviolated. > > It seems like it performs the checking on the level of individual field > write, not SQL statements. I suggested earlier to have "CONSTRAINT LEVEL > [WRITE|STATEMENT|TRANSACTION];" > It seems they currently work on the level of field writes and transactions, > and not on SQL statement level.
These (largely overlapping) pages <https://www.sqlite.org/conflict.html> <https://sqlite.org/lang_conflict.html> seem to describe UNIQUE, NOT NULL, and CHECK constraints being checked at the statement level. Which is the behaviour you're seeing. However this is not the behaviour I've seen described for SQLite in the past. I'm sure I've read somewhere that consistency checks are checked at COMMIT time. Maybe that's just for the default FOREIGN KEY checks mentioned upthread: <https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys> So SQLite is not consistent. It checks some things at the statement level and others at the transaction level. Which is probably not good. Simon.
