CODEWITHSUNDEEP
sqlitesql-insertupsert
chovy
chovy

Reputation: 75686

How to get ON CONFLICT IGNORE working in sqlite

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

Upvotes: 17

Views: 15671

Answers (1)

forpas
forpas

Reputation: 164099

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.

Upvotes: 28

Related Questions