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.
Cheers,
Mick O'Neill
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
