1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2001 September 27 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is testing the CREATE UNIQUE INDEX statement, 147c478bd9Sstevel@tonic-gate# and primary keys, and the UNIQUE constraint on table columns 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gate# $Id: unique.test,v 1.7 2003/08/05 13:13:39 drh Exp $ 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gate# Try to create a table with two primary keys. 227c478bd9Sstevel@tonic-gate# (This is allowed in SQLite even that it is not valid SQL) 237c478bd9Sstevel@tonic-gate# 247c478bd9Sstevel@tonic-gatedo_test unique-1.1 { 257c478bd9Sstevel@tonic-gate catchsql { 267c478bd9Sstevel@tonic-gate CREATE TABLE t1( 277c478bd9Sstevel@tonic-gate a int PRIMARY KEY, 287c478bd9Sstevel@tonic-gate b int PRIMARY KEY, 297c478bd9Sstevel@tonic-gate c text 307c478bd9Sstevel@tonic-gate ); 317c478bd9Sstevel@tonic-gate } 327c478bd9Sstevel@tonic-gate} {1 {table "t1" has more than one primary key}} 337c478bd9Sstevel@tonic-gatedo_test unique-1.1b { 347c478bd9Sstevel@tonic-gate catchsql { 357c478bd9Sstevel@tonic-gate CREATE TABLE t1( 367c478bd9Sstevel@tonic-gate a int PRIMARY KEY, 377c478bd9Sstevel@tonic-gate b int UNIQUE, 387c478bd9Sstevel@tonic-gate c text 397c478bd9Sstevel@tonic-gate ); 407c478bd9Sstevel@tonic-gate } 417c478bd9Sstevel@tonic-gate} {0 {}} 427c478bd9Sstevel@tonic-gatedo_test unique-1.2 { 437c478bd9Sstevel@tonic-gate catchsql { 447c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c) VALUES(1,2,3) 457c478bd9Sstevel@tonic-gate } 467c478bd9Sstevel@tonic-gate} {0 {}} 477c478bd9Sstevel@tonic-gatedo_test unique-1.3 { 487c478bd9Sstevel@tonic-gate catchsql { 497c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c) VALUES(1,3,4) 507c478bd9Sstevel@tonic-gate } 517c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 527c478bd9Sstevel@tonic-gatedo_test unique-1.4 { 537c478bd9Sstevel@tonic-gate execsql { 547c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 557c478bd9Sstevel@tonic-gate } 567c478bd9Sstevel@tonic-gate} {1 2 3} 577c478bd9Sstevel@tonic-gatedo_test unique-1.5 { 587c478bd9Sstevel@tonic-gate catchsql { 597c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c) VALUES(3,2,4) 607c478bd9Sstevel@tonic-gate } 617c478bd9Sstevel@tonic-gate} {1 {column b is not unique}} 627c478bd9Sstevel@tonic-gatedo_test unique-1.6 { 637c478bd9Sstevel@tonic-gate execsql { 647c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 657c478bd9Sstevel@tonic-gate } 667c478bd9Sstevel@tonic-gate} {1 2 3} 677c478bd9Sstevel@tonic-gatedo_test unique-1.7 { 687c478bd9Sstevel@tonic-gate catchsql { 697c478bd9Sstevel@tonic-gate INSERT INTO t1(a,b,c) VALUES(3,4,5) 707c478bd9Sstevel@tonic-gate } 717c478bd9Sstevel@tonic-gate} {0 {}} 727c478bd9Sstevel@tonic-gatedo_test unique-1.8 { 737c478bd9Sstevel@tonic-gate execsql { 747c478bd9Sstevel@tonic-gate SELECT * FROM t1 ORDER BY a; 757c478bd9Sstevel@tonic-gate } 767c478bd9Sstevel@tonic-gate} {1 2 3 3 4 5} 777c478bd9Sstevel@tonic-gateintegrity_check unique-1.9 787c478bd9Sstevel@tonic-gate 797c478bd9Sstevel@tonic-gatedo_test unique-2.0 { 807c478bd9Sstevel@tonic-gate execsql { 817c478bd9Sstevel@tonic-gate DROP TABLE t1; 827c478bd9Sstevel@tonic-gate CREATE TABLE t2(a int, b int); 837c478bd9Sstevel@tonic-gate INSERT INTO t2(a,b) VALUES(1,2); 847c478bd9Sstevel@tonic-gate INSERT INTO t2(a,b) VALUES(3,4); 857c478bd9Sstevel@tonic-gate SELECT * FROM t2 ORDER BY a; 867c478bd9Sstevel@tonic-gate } 877c478bd9Sstevel@tonic-gate} {1 2 3 4} 887c478bd9Sstevel@tonic-gatedo_test unique-2.1 { 897c478bd9Sstevel@tonic-gate catchsql { 907c478bd9Sstevel@tonic-gate CREATE UNIQUE INDEX i2 ON t2(a) 917c478bd9Sstevel@tonic-gate } 927c478bd9Sstevel@tonic-gate} {0 {}} 937c478bd9Sstevel@tonic-gatedo_test unique-2.2 { 947c478bd9Sstevel@tonic-gate catchsql { 957c478bd9Sstevel@tonic-gate SELECT * FROM t2 ORDER BY a 967c478bd9Sstevel@tonic-gate } 977c478bd9Sstevel@tonic-gate} {0 {1 2 3 4}} 987c478bd9Sstevel@tonic-gatedo_test unique-2.3 { 997c478bd9Sstevel@tonic-gate catchsql { 1007c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(1,5); 1017c478bd9Sstevel@tonic-gate } 1027c478bd9Sstevel@tonic-gate} {1 {column a is not unique}} 1037c478bd9Sstevel@tonic-gatedo_test unique-2.4 { 1047c478bd9Sstevel@tonic-gate catchsql { 1057c478bd9Sstevel@tonic-gate SELECT * FROM t2 ORDER BY a 1067c478bd9Sstevel@tonic-gate } 1077c478bd9Sstevel@tonic-gate} {0 {1 2 3 4}} 1087c478bd9Sstevel@tonic-gatedo_test unique-2.5 { 1097c478bd9Sstevel@tonic-gate catchsql { 1107c478bd9Sstevel@tonic-gate DROP INDEX i2; 1117c478bd9Sstevel@tonic-gate SELECT * FROM t2 ORDER BY a; 1127c478bd9Sstevel@tonic-gate } 1137c478bd9Sstevel@tonic-gate} {0 {1 2 3 4}} 1147c478bd9Sstevel@tonic-gatedo_test unique-2.6 { 1157c478bd9Sstevel@tonic-gate catchsql { 1167c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(1,5) 1177c478bd9Sstevel@tonic-gate } 1187c478bd9Sstevel@tonic-gate} {0 {}} 1197c478bd9Sstevel@tonic-gatedo_test unique-2.7 { 1207c478bd9Sstevel@tonic-gate catchsql { 1217c478bd9Sstevel@tonic-gate SELECT * FROM t2 ORDER BY a, b; 1227c478bd9Sstevel@tonic-gate } 1237c478bd9Sstevel@tonic-gate} {0 {1 2 1 5 3 4}} 1247c478bd9Sstevel@tonic-gatedo_test unique-2.8 { 1257c478bd9Sstevel@tonic-gate catchsql { 1267c478bd9Sstevel@tonic-gate CREATE UNIQUE INDEX i2 ON t2(a); 1277c478bd9Sstevel@tonic-gate } 1287c478bd9Sstevel@tonic-gate} {1 {indexed columns are not unique}} 1297c478bd9Sstevel@tonic-gatedo_test unique-2.9 { 1307c478bd9Sstevel@tonic-gate catchsql { 1317c478bd9Sstevel@tonic-gate CREATE INDEX i2 ON t2(a); 1327c478bd9Sstevel@tonic-gate } 1337c478bd9Sstevel@tonic-gate} {0 {}} 1347c478bd9Sstevel@tonic-gateintegrity_check unique-2.10 1357c478bd9Sstevel@tonic-gate 1367c478bd9Sstevel@tonic-gate# Test the UNIQUE keyword as used on two or more fields. 1377c478bd9Sstevel@tonic-gate# 1387c478bd9Sstevel@tonic-gatedo_test unique-3.1 { 1397c478bd9Sstevel@tonic-gate catchsql { 1407c478bd9Sstevel@tonic-gate CREATE TABLE t3( 1417c478bd9Sstevel@tonic-gate a int, 1427c478bd9Sstevel@tonic-gate b int, 1437c478bd9Sstevel@tonic-gate c int, 1447c478bd9Sstevel@tonic-gate d int, 1457c478bd9Sstevel@tonic-gate unique(a,c,d) 1467c478bd9Sstevel@tonic-gate ); 1477c478bd9Sstevel@tonic-gate } 1487c478bd9Sstevel@tonic-gate} {0 {}} 1497c478bd9Sstevel@tonic-gatedo_test unique-3.2 { 1507c478bd9Sstevel@tonic-gate catchsql { 1517c478bd9Sstevel@tonic-gate INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4); 1527c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY a,b,c,d; 1537c478bd9Sstevel@tonic-gate } 1547c478bd9Sstevel@tonic-gate} {0 {1 2 3 4}} 1557c478bd9Sstevel@tonic-gatedo_test unique-3.3 { 1567c478bd9Sstevel@tonic-gate catchsql { 1577c478bd9Sstevel@tonic-gate INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5); 1587c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY a,b,c,d; 1597c478bd9Sstevel@tonic-gate } 1607c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 1 2 3 5}} 1617c478bd9Sstevel@tonic-gatedo_test unique-3.4 { 1627c478bd9Sstevel@tonic-gate catchsql { 1637c478bd9Sstevel@tonic-gate INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5); 1647c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY a,b,c,d; 1657c478bd9Sstevel@tonic-gate } 1667c478bd9Sstevel@tonic-gate} {1 {columns a, c, d are not unique}} 1677c478bd9Sstevel@tonic-gateintegrity_check unique-3.5 1687c478bd9Sstevel@tonic-gate 1697c478bd9Sstevel@tonic-gate# Make sure NULLs are distinct as far as the UNIQUE tests are 1707c478bd9Sstevel@tonic-gate# concerned. 1717c478bd9Sstevel@tonic-gate# 1727c478bd9Sstevel@tonic-gatedo_test unique-4.1 { 1737c478bd9Sstevel@tonic-gate execsql { 1747c478bd9Sstevel@tonic-gate CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c)); 1757c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(1,2,3); 1767c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(NULL, 2, NULL); 1777c478bd9Sstevel@tonic-gate SELECT * FROM t4; 1787c478bd9Sstevel@tonic-gate } 1797c478bd9Sstevel@tonic-gate} {1 2 3 {} 2 {}} 1807c478bd9Sstevel@tonic-gatedo_test unique-4.2 { 1817c478bd9Sstevel@tonic-gate catchsql { 1827c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(NULL, 3, 4); 1837c478bd9Sstevel@tonic-gate } 1847c478bd9Sstevel@tonic-gate} {0 {}} 1857c478bd9Sstevel@tonic-gatedo_test unique-4.3 { 1867c478bd9Sstevel@tonic-gate execsql { 1877c478bd9Sstevel@tonic-gate SELECT * FROM t4 1887c478bd9Sstevel@tonic-gate } 1897c478bd9Sstevel@tonic-gate} {1 2 3 {} 2 {} {} 3 4} 1907c478bd9Sstevel@tonic-gatedo_test unique-4.4 { 1917c478bd9Sstevel@tonic-gate catchsql { 1927c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(2, 2, NULL); 1937c478bd9Sstevel@tonic-gate } 1947c478bd9Sstevel@tonic-gate} {0 {}} 1957c478bd9Sstevel@tonic-gatedo_test unique-4.5 { 1967c478bd9Sstevel@tonic-gate execsql { 1977c478bd9Sstevel@tonic-gate SELECT * FROM t4 1987c478bd9Sstevel@tonic-gate } 1997c478bd9Sstevel@tonic-gate} {1 2 3 {} 2 {} {} 3 4 2 2 {}} 2007c478bd9Sstevel@tonic-gateintegrity_check unique-4.6 2017c478bd9Sstevel@tonic-gate 2027c478bd9Sstevel@tonic-gate# Test the error message generation logic. In particular, make sure we 2037c478bd9Sstevel@tonic-gate# do not overflow the static buffer used to generate the error message. 2047c478bd9Sstevel@tonic-gate# 2057c478bd9Sstevel@tonic-gatedo_test unique-5.1 { 2067c478bd9Sstevel@tonic-gate execsql { 2077c478bd9Sstevel@tonic-gate CREATE TABLE t5( 2087c478bd9Sstevel@tonic-gate first_column_with_long_name, 2097c478bd9Sstevel@tonic-gate second_column_with_long_name, 2107c478bd9Sstevel@tonic-gate third_column_with_long_name, 2117c478bd9Sstevel@tonic-gate fourth_column_with_long_name, 2127c478bd9Sstevel@tonic-gate fifth_column_with_long_name, 2137c478bd9Sstevel@tonic-gate sixth_column_with_long_name, 2147c478bd9Sstevel@tonic-gate UNIQUE( 2157c478bd9Sstevel@tonic-gate first_column_with_long_name, 2167c478bd9Sstevel@tonic-gate second_column_with_long_name, 2177c478bd9Sstevel@tonic-gate third_column_with_long_name, 2187c478bd9Sstevel@tonic-gate fourth_column_with_long_name, 2197c478bd9Sstevel@tonic-gate fifth_column_with_long_name, 2207c478bd9Sstevel@tonic-gate sixth_column_with_long_name 2217c478bd9Sstevel@tonic-gate ) 2227c478bd9Sstevel@tonic-gate ); 2237c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(1,2,3,4,5,6); 2247c478bd9Sstevel@tonic-gate SELECT * FROM t5; 2257c478bd9Sstevel@tonic-gate } 2267c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6} 2277c478bd9Sstevel@tonic-gatedo_test unique-5.2 { 2287c478bd9Sstevel@tonic-gate catchsql { 2297c478bd9Sstevel@tonic-gate INSERT INTO t5 VALUES(1,2,3,4,5,6); 2307c478bd9Sstevel@tonic-gate } 2317c478bd9Sstevel@tonic-gate} {1 {columns first_column_with_long_name, second_column_with_long_name, third_column_with_long_name, fourth_column_with_long_name, fifth_column_with_long_name, ... are not unique}} 2327c478bd9Sstevel@tonic-gate 2337c478bd9Sstevel@tonic-gatefinish_test 234