What Is Meant by "Conflict Target" in SQLite?

When performing an UPSERT operation in SQLite, a "conflict target" refers to column(s) having a uniqueness constraint that triggers an UPSERT. It is specified in the ON CONFLICT clause and may be omitted in the last clause of the INSERT statement.

If the conflict target is omitted in the last INSERT statement, it will trigger for any uniqueness constraint violation that was not captured by prior ON CONFLICT clauses.

If the INSERT operation would violate the conflict target, the UPSERT performs a "DO NOTHING" or "DO UPDATE" operation instead of inserting the row. This means that for each row of the INSERT operation, only the first ON CONFLICT clause that matches the conflict target is executed. If none of the ON CONFLICT clauses match the conflict target, the INSERT operation proceeds normally and inserts the row.

For example:

-- SQLite 3.24.0+
INSERT INTO `users` (`username`, `date_login`)
  VALUES ('john', '2023-04-02 00:00:00')
  ON CONFLICT (`name`) DO UPDATE SET `date_login` = datetime('now');

In this example, the "username" column is the conflict target, where:

  • If no conflict occurs with other "username" values, then a new row is inserted;
  • If a conflict occurs where a row with the same "username" already exists in the table, SQLite will update the "date_login" value of the conflicting row with the current date/time instead of inserting a new row.

This post was published (and was last revised ) by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.