2009/10/31 Mick <[email protected]>: > 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. > > 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); > > Creates 2 records with the same primary key. It makes no difference whether > you use "OR IGNORE" in the insert statements, either.
NULL != NULL, so the primary key is not conflicting > > Cheers, > > Mick O'Neill > Regards, Simon _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
