With SQLite, INSERT OR IGNORE Is Often Not What You Want

tl;dr - INSERT OR IGNORE will silently fail if any constraint is violated (even a NOT NULL on a column type!). If you want to express "insert this row unless it already exists", use ON CONFLICT instead.

I've been bitten by this behavior at least three times over the last few years, so I'm going to leave it here for others, but also for myself to seal in the lesson.

The most recent episode of this for me was this: I was working on a program to insert messages into a SQLite database for easier (well, more powerful) searching. The data dumps I'm processing may contain messages I've already inserted, and I don't want to insert those twice, so I initially reached for INSERT OR IGNORE. Later on, I noticed that I was processing some messages, but they weren't making it into the database at all. When I dug in further, it turns out I was inserting NULL into a NOT NULL column, yet I wasn't getting an exception - because INSERT OR IGNORE will ignore those violations!

The proper solve was to use ON CONFLICT(conversation_id, timestamp) DO NOTHING instead - this expresses my original intent of "don't worry if there's already a row for the given conversation and timestamp, but do still worry if other violations occur".

Also, I think you could use INSERT OR REPLACE - but this will end up creating a whole new row, which takes time, and it will have a different rowid, which can screw up foreign key relationships.