sqlite – How do I do an atomic INSERT IF NOT EXISTS equivalent in sqlite3? – Education Career Blog

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?

,

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

,

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;

Leave a Comment