SQLite
View Ticket
Not logged in
Ticket UUID: 9a6daf340df99ba93c53bcf8fa83d9f28040d2a8
Title: UNIQUE not enforced on an index
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-07-30 14:44:37
Version Found In: 3.8.5
User Comments:
drh added on 2014-07-30 12:33:04:

The CREATE UNIQUE INDEX on the third line below does not fail even though the x column values are not all unique.

CREATE TABLE t1(x NOT NULL);
INSERT INTO t1 VALUES(1),(2),(2),(3);
CREATE UNIQUE INDEX t1x ON t1(x);

If the index is created before the INSERT, then uniqueness is enforced at the point of the INSERT. Note that the NOT NULL on the indexed column seems to be required in order to exhibit this bug.

"PRAGMA integrity_check" does not detect the resulting malformed database. That might be considered a separate issue.

Bisecting shows that this problem was introduced by the addition of WITHOUT ROWID support in version 3.8.2, specifically in check-in c80e229dd9c1230 on 2013-11-07. This problem was reported on the mailing list by Pavel Pimenov.