sqlite - How to get INSERT OR IGNORE to work

I'm trying to insert data into a table. I would like to insert the row if the column doesn't have the data already - regardless of the other columns.

CREATE TABLE t ( id INTEGER PRIMARY KEY, name VARCHAR, other INT ); INSERT OR IGNORE INTO t (name) VALUES ('a'); INSERT OR IGNORE INTO t (name) VALUES ('a'); INSERT OR IGNORE INTO t (name) VALUES ('a'); 

With the above snippet I end up with 3 rows, not 1 as I would have thought. If it matters the actual sql is happening inside of a INSTEAD OF INSERT trigger, this is just a simple test case.

2 Answers

Replace

CREATE TABLE t ( id INTEGER PRIMARY KEY, name VARCHAR, other INT ); 

with

CREATE TABLE t ( id INTEGER PRIMARY KEY, name VARCHAR UNIQUE, other INT ); 

Then you will get

sqlite> CREATE TABLE t ( ...> id INTEGER PRIMARY KEY, ...> name VARCHAR UNIQUE, ...> other INT ...> ); sqlite> INSERT OR IGNORE INTO t (name) VALUES ('a'); sqlite> INSERT OR IGNORE INTO t (name) VALUES ('a'); sqlite> INSERT OR IGNORE INTO t (name) VALUES ('a'); sqlite> select * from t ; 1|a| 

That would only work for the primary key field or unique constraints:

The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one INSERT command.

Further:

The ON CONFLICT clause applies to UNIQUE and NOT NULL constraints (and to PRIMARY KEY constraints which for the purposes of this section are the same thing as UNIQUE constraints). The ON CONFLICT algorithm does not apply to FOREIGN KEY constraints. There are five conflict resolution algorithm choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default conflict resolution algorithm is ABORT.