9

I'm trying to ignore inserts if a tag and url combo exist already:

INSERT INTO tags(tag, url) VALUES (?, ?); 
              ON CONFLICT(url, tag) IGNORE

I have a UNIQUE INDEX on (tag, url)

    CREATE UNIQUE INDEX tag_url ON tags (tag, url)

The app just crashes saying [uncaught application error]: SqliteError - UNIQUE constraint failed: tags.tag, tags.url

forpas
  • 160,666
  • 10
  • 38
  • 76
chovy
  • 72,281
  • 52
  • 227
  • 295

1 Answers1

18

You are using SQLite's UPSERT syntax incorrectly.

First, you have a ; right after the INSERT statement and before ON CONFLICT, which separates the statements as different statements.
This means that the INSERT statement is executed as a simple statement (without the ON CONFLICT clause making any difference), so you get the UNIQUE constraint failed error.

Also, you are using the keyword IGNORE instead of DO NOTHING.

Change your code to:

INSERT INTO tags(tag, url) VALUES (?, ?)
ON CONFLICT(tag, url) DO NOTHING;

You could achieve the same with the INSERT OR IGNORE syntax:

INSERT OR IGNORE INTO tags(tag, url) VALUES (?, ?);

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Very good solution, I used it to make a "bulk" action of passing messages to "already seen" in my app : INSERT OR IGNORE INTO old_messages SELECT id FROM new_messages" – Joe Mama Jr. Jan 24 '23 at 08:50