Index: test/unique.test ================================================================== --- test/unique.test +++ test/unique.test @@ -10,11 +10,11 @@ #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE UNIQUE INDEX statement, # and primary keys, and the UNIQUE constraint on table columns # -# $Id: unique.test,v 1.1 2001/09/27 15:11:55 drh Exp $ +# $Id: unique.test,v 1.2 2001/09/27 23:57:06 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to create a table with two primary keys. @@ -27,7 +27,130 @@ b int PRIMARY KEY, c text ); } } {0 {}} +do_test unique-1.2 { + catchsql { + INSERT INTO t1(a,b,c) VALUES(1,2,3) + } +} {0 {}} +do_test unique-1.3 { + catchsql { + INSERT INTO t1(a,b,c) VALUES(1,3,4) + } +} {1 {constraint failed}} +do_test unique-1.4 { + execsql { + SELECT * FROM t1 ORDER BY a; + } +} {1 2 3} +do_test unique-1.5 { + catchsql { + INSERT INTO t1(a,b,c) VALUES(3,2,4) + } +} {1 {constraint failed}} +do_test unique-1.6 { + execsql { + SELECT * FROM t1 ORDER BY a; + } +} {1 2 3} +do_test unique-1.7 { + catchsql { + INSERT INTO t1(a,b,c) VALUES(3,4,5) + } +} {0 {}} +do_test unique-1.8 { + execsql { + SELECT * FROM t1 ORDER BY a; + } +} {1 2 3 3 4 5} + +do_test unique-2.0 { + execsql { + DROP TABLE t1; + CREATE TABLE t2(a int, b int); + INSERT INTO t2(a,b) VALUES(1,2); + INSERT INTO t2(a,b) VALUES(3,4); + SELECT * FROM t2 ORDER BY a; + } +} {1 2 3 4} +do_test unique-2.1 { + catchsql { + CREATE UNIQUE INDEX i2 ON t2(a) + } +} {0 {}} +do_test unique-2.2 { + catchsql { + SELECT * FROM t2 ORDER BY a + } +} {0 {1 2 3 4}} +do_test unique-2.3 { + catchsql { + INSERT INTO t2 VALUES(1,5); + } +} {1 {constraint failed}} +do_test unique-2.4 { + catchsql { + SELECT * FROM t2 ORDER BY a + } +} {0 {1 2 3 4}} +do_test unique-2.5 { + catchsql { + DROP INDEX i2; + SELECT * FROM t2 ORDER BY a; + } +} {0 {1 2 3 4}} +do_test unique-2.6 { + catchsql { + INSERT INTO t2 VALUES(1,5) + } +} {0 {}} +do_test unique-2.7 { + catchsql { + SELECT * FROM t2 ORDER BY a, b; + } +} {0 {1 2 1 5 3 4}} +do_test unique-2.8 { + catchsql { + CREATE UNIQUE INDEX i2 ON t2(a); + } +} {1 {constraint failed}} +do_test unique-2.9 { + catchsql { + CREATE INDEX i2 ON t2(a); + } +} {0 {}} + +# Test the UNIQUE keyword as used on two or more fields. +# +do_test unique-3.1 { + catchsql { + CREATE TABLE t3( + a int, + b int, + c int, + d int, + unique(a,c,d) + ); + } +} {0 {}} +do_test unique-3.2 { + catchsql { + INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4); + SELECT * FROM t3 ORDER BY a,b,c,d; + } +} {0 {1 2 3 4}} +do_test unique-3.3 { + catchsql { + INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5); + SELECT * FROM t3 ORDER BY a,b,c,d; + } +} {0 {1 2 3 4 1 2 3 5}} +do_test unique-3.4 { + catchsql { + INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5); + SELECT * FROM t3 ORDER BY a,b,c,d; + } +} {1 {constraint failed}} finish_test