1# 2014-07-30 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this file is testing the CREATE UNIQUE INDEX statement 13# to verify that ticket 9a6daf340df99ba93c53bcf8fa83d9f28040d2a8 14# has been fixed: 15# 16# drh added on 2014-07-30 12:33:04: 17# 18# The CREATE UNIQUE INDEX on the third line below does not fail even 19# though the x column values are not all unique. 20# 21# CREATE TABLE t1(x NOT NULL); 22# INSERT INTO t1 VALUES(1),(2),(2),(3); 23# CREATE UNIQUE INDEX t1x ON t1(x); 24# 25# If the index is created before the INSERT, then uniqueness is enforced 26# at the point of the INSERT. Note that the NOT NULL on the indexed column 27# seems to be required in order to exhibit this bug. 28# 29# "PRAGMA integrity_check" does not detect the resulting malformed database. 30# That might be considered a separate issue. 31# 32# Bisecting shows that this problem was introduced by the addition of 33# WITHOUT ROWID support in version 3.8.2, specifically in check-in 34# [c80e229dd9c1230] on 2013-11-07. This problem was reported on the mailing 35# list by Pavel Pimenov. and primary keys, and the UNIQUE constraint 36# on table columns 37# 38 39set testdir [file dirname $argv0] 40source $testdir/tester.tcl 41 42foreach {id sql} { 43 1 {CREATE TABLE t1(x TEXT PRIMARY KEY, y NOT NULL) WITHOUT ROWID} 44 2 {CREATE TABLE t1(x TEXT PRIMARY KEY, y NOT NULL)} 45 3 {CREATE TABLE t1(x TEXT PRIMARY KEY, y) WITHOUT ROWID} 46 4 {CREATE TABLE t1(x TEXT PRIMARY KEY, y)} 47} { 48 do_test $id.1 { 49 db eval {DROP TABLE IF EXISTS t1} 50 db eval $sql 51 db eval {INSERT INTO t1(x,y) VALUES(1,1),(2,2),(3,2),(4,3)} 52 } {} 53 do_test $id.2 { 54 catchsql {CREATE UNIQUE INDEX t1y ON t1(y)} 55 } {1 {UNIQUE constraint failed: t1.y}} 56} 57 58foreach {id sql} { 59 5 {CREATE TABLE t1(w,x,y NOT NULL,z NOT NULL,PRIMARY KEY(w,x)) WITHOUT ROWID} 60 6 {CREATE TABLE t1(w,x,y NOT NULL,z NOT NULL,PRIMARY KEY(w,x))} 61 7 {CREATE TABLE t1(w,x,y NOT NULL,z,PRIMARY KEY(w,x)) WITHOUT ROWID} 62 8 {CREATE TABLE t1(w,x,y NOT NULL,z,PRIMARY KEY(w,x))} 63 9 {CREATE TABLE t1(w,x,y,z NOT NULL,PRIMARY KEY(w,x)) WITHOUT ROWID} 64 10 {CREATE TABLE t1(w,x,y,z NOT NULL,PRIMARY KEY(w,x))} 65 11 {CREATE TABLE t1(w,x,y,z,PRIMARY KEY(w,x)) WITHOUT ROWID} 66 12 {CREATE TABLE t1(w,x,y,z,PRIMARY KEY(w,x))} 67} { 68 do_test $id.1 { 69 db eval {DROP TABLE IF EXISTS t1} 70 db eval $sql 71 db eval {INSERT INTO t1(w,x,y,z) VALUES(1,2,3,4),(2,3,3,4)} 72 } {} 73 do_test $id.2 { 74 catchsql {CREATE UNIQUE INDEX t1yz ON t1(y,z)} 75 } {1 {UNIQUE constraint failed: t1.y, t1.z}} 76} 77 78do_catchsql_test 13.1 { 79 CREATE TABLE err1(a,b,c,UNIQUE(rowid)); 80} {1 {no such column: rowid}} 81do_catchsql_test 13.2 { 82 CREATE TABLE err1(a,b,c,PRIMARY KEY(rowid)); 83} {1 {no such column: rowid}} 84 85 86finish_test 87