/ View Ticket
Login
2014-07-30
14:57
Merge in the CREATE UNIQUE INDEX fix of ticket [9a6daf340df99ba93c53bcf]. (check-in: fa791232 user: drh tags: apple-osx)
14:44 Fixed ticket [9a6daf34]: UNIQUE not enforced on an index plus 5 other changes (artifact: 30408f14 user: drh)
14:44
Merge recent trunk changes, and especially the fix for the CREATE UNIQUE INDEX problem of ticket [9a6daf340df99ba9]. (check-in: 5b50a838 user: drh tags: threads)
13:56
Ensure that the correct number of columns in a UNIQUE index are checked for uniqueness, regardless of whether or not the original table has a ROWID or if the columns are NOT NULL, etc. Ticket [9a6daf340df99ba93c]. (check-in: 6b785e92 user: drh tags: trunk)
12:33 New ticket [9a6daf34] UNIQUE not enforced on an index. (artifact: 84f3a315 user: drh)

Ticket Hash: 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.