How to Fix "ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint" SQLite Error?

When performing an UPSERT using the "ON CONFLICT" clause, you must specify a unique conflict target — i.e. column(s) having a unique constraint or unique index. Otherwise, the following error is thrown:

-- Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

To fix this error, you can do either of the following:

• Add a PRIMARY KEY or UNIQUE constraint to the column(s) you want to use the "ON CONFLICT" clause on, or;
• Remove the "ON CONFLICT" clause from your "INSERT" statement.

For example, if you have a table called "users" with columns "id" and "email", and you want to use the "ON CONFLICT" clause on the "email" column, you could add a UNIQUE constraint to the "email" column in the following ways:

CREATE TABLE `users` (
  `id` INTEGER PRIMARY KEY,
  `email` VARCHAR(250) NOT NULL UNIQUE
);

CREATE TABLE `users` (
  `id` INTEGER PRIMARY KEY,
  `email` VARCHAR(250) NOT NULL,
  CONSTRAINT `uq_email` UNIQUE (`email`)
);

Alternatively, if you do not need conflict handling for the "email" column, you could remove the "ON CONFLICT" clause from your "INSERT" statement:

INSERT INTO `users` (`id`, `email`) VALUES (1, '[email protected]');