On Sat, Oct 31, 2009 at 08:31:01AM -0400, D. Richard Hipp wrote: > > On Oct 30, 2009, at 10:25 PM, Mick wrote: > > > This is more FYI than needing it (as I have already worked around > > it), but I > > have discovered that an IGNORE constraint on an insert, when one of > > the > > fields in the constraint is NULL, will insert a duplicate record > > into the > > database. > > This is not a bug. See, for example, the explanation on > http://www.sqlite.org/cvstrac/tktview?tn=3463 > > > > > i.e. > > > > CREATE TABLE mytable ( > > ID1 INTEGER NOT NULL, > > ID2 INTEGER NOT NULL, > > SomeText VARCHAR(100) COLLATE NOCASE, > > PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE); > > > > INSERT INTO mytable VALUES (1, 1, NULL); > > INSERT INTO mytable VALUES (1, 1, NULL);
As an aside, Postgres 8.4 does not allow the insert to happen in the first place. http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html "The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values." [jer...@[local]] 12:59:21> create table mytable( id1 integer not null, id2 integer not null, sometext varchar(100), primary key(id1, id2, sometext)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "mytable_pkey" for table "mytable" CREATE TABLE Time: 492.766 ms [jer...@[local]] 13:00:04> insert into mytable values(1,1,NULL); ERROR: null value in column "sometext" violates not-null constraint Change the PRIMARY KEY to a UNIQUE contraint and sqlite and postgres agree on the behavior. On the same postgresql page: "For the purpose of a unique constraint, null values are not considered equal." [jer...@[local]] 13:03:32> create table mytable( id1 integer not null, id2 integer not null, sometext varchar(100), unique(id1, id2, sometext)); NOTICE: CREATE TABLE / UNIQUE will create implicit index "mytable_id1_key" for table "mytable" CREATE TABLE Time: 121.473 ms [jer...@[local]] 13:03:49> insert into mytable values(1,1,NULL); INSERT 0 1 Time: 18.476 ms [jer...@[local]] 13:03:54> insert into mytable values(1,1,NULL); INSERT 0 1 Time: 0.539 ms [jer...@[local]] 13:03:56> select * from mytable; id1 | id2 | sometext -----+-----+---------- 1 | 1 | NULL 1 | 1 | NULL (2 rows) Time: 14.775 ms enjoy, -jeremy -- ======================================================================== Jeremy Hinegardner jer...@hinegardner.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
