# 2001 September 27 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # 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.3 2001/12/21 14:30:44 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to create a table with two primary keys. # (This is allowed in SQLite even that it is not valid SQL) # do_test unique-1.1 { catchsql { CREATE TABLE t1( a int PRIMARY KEY, b int PRIMARY KEY, c text ); } } {1 {table "t1" has more than one primary key}} do_test unique-1.1b { catchsql { CREATE TABLE t1( a int PRIMARY KEY, b int UNIQUE, 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