sqlite - How to get INSERT OR IGNORE to work

Question

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.

Answer 1

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|