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|