How do I do an atomic INSERT IF NOT EXISTS equivalent in sqlite3?

Question

The questions says it all really.

I have a table and I want to insert a row if it doesn't already exist. or should I just do an insert and if the key constraint is violated then ignore it?

Answer 1

Use a trigger that fires before INSERTs and discards the duplicate row, something along the lines of...

CREATE TRIGGER trigger_name
BEFORE INSERT on your_table
FOR EACH ROW WHEN EXISTS (SELECT * FROM your_table WHERE id = NEW.id)
BEGIN
  SELECT RAISE(IGNORE);
END;

Answer 2

Use INSERT OR IGNORE: http://www.sqlite.org/lang_insert.html