AryaWu/sqlite
0
1# 2010 September 252#3# The author disclaims copyright to this source code. In place of4# 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#12# This file implements tests to verify that the "testable statements" in 13# the lang_createtable.html document are correct.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19set ::testprefix e_createtable20 21# Test organization:22#23# e_createtable-0.*: Test that the syntax diagrams are correct.24#25# e_createtable-1.*: Test statements related to table and database names, 26# the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.27#28# e_createtable-2.*: Test "CREATE TABLE AS" statements.29#30 31proc do_createtable_tests {nm args} {32 uplevel do_select_tests [list e_createtable-$nm] $args33}34 35 36#-------------------------------------------------------------------------37# This command returns a serialized tcl array mapping from the name of38# each attached database to a list of tables in that database. For example,39# if the database schema is created with:40#41# CREATE TABLE t1(x);42# CREATE TEMP TABLE t2(x);43# CREATE TEMP TABLE t3(x);44#45# Then this command returns "main t1 temp {t2 t3}".46#47proc table_list {} {48 set res [list]49 db eval { pragma database_list } a {50 set dbname $a(name)51 set master $a(name).sqlite_master52 if {$dbname == "temp"} { set master sqlite_temp_master }53 lappend res $dbname [54 db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"55 ]56 }57 set res58}59 60 61do_createtable_tests 0.1.1 -repair {62 drop_all_tables63} {64 1 "CREATE TABLE t1(c1 one)" {}65 2 "CREATE TABLE t1(c1 one two)" {}66 3 "CREATE TABLE t1(c1 one two three)" {}67 4 "CREATE TABLE t1(c1 one two three four)" {}68 5 "CREATE TABLE t1(c1 one two three four(14))" {}69 6 "CREATE TABLE t1(c1 one two three four(14, 22))" {}70 7 "CREATE TABLE t1(c1 var(+14, -22.3))" {}71 8 "CREATE TABLE t1(c1 var(1.0e10))" {}72}73do_createtable_tests 0.1.2 -error {74 near "%s": syntax error75} {76 1 "CREATE TABLE t1(c1 one(number))" {number}77}78 79 80# syntax diagram column-constraint81#82do_createtable_tests 0.2.1 -repair {83 drop_all_tables 84 execsql { CREATE TABLE t2(x PRIMARY KEY) }85} {86 1.1 "CREATE TABLE t1(c1 text PRIMARY KEY)" {}87 1.2 "CREATE TABLE t1(c1 text PRIMARY KEY ASC)" {}88 1.3 "CREATE TABLE t1(c1 text PRIMARY KEY DESC)" {}89 1.4 "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)" {}90 91 2.1 "CREATE TABLE t1(c1 text NOT NULL)" {}92 2.2 "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)" {}93 2.3 "CREATE TABLE t1(c1 text NULL)" {}94 2.4 "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)" {}95 96 3.1 "CREATE TABLE t1(c1 text UNIQUE)" {}97 3.2 "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)" {}98 99 4.1 "CREATE TABLE t1(c1 text CHECK(c1!=0))" {}100 4.2 "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))" {}101 102 5.1 "CREATE TABLE t1(c1 text DEFAULT 1)" {}103 5.2 "CREATE TABLE t1(c1 text DEFAULT -1)" {}104 5.3 "CREATE TABLE t1(c1 text DEFAULT +1)" {}105 5.4 "CREATE TABLE t1(c1 text DEFAULT -45.8e22)" {}106 5.5 "CREATE TABLE t1(c1 text DEFAULT (1+1))" {}107 5.6 "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))" {}108 109 6.1 "CREATE TABLE t1(c1 text COLLATE nocase)" {}110 6.2 "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)" {}111 112 7.1 "CREATE TABLE t1(c1 REFERENCES t2)" {}113 7.2 "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)" {}114 115 8.1 {116 CREATE TABLE t1(c1 117 PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1118 );119 } {}120 8.2 {121 CREATE TABLE t1(c1 122 REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY 123 );124 } {}125}126 127# -- syntax diagram table-constraint128#129do_createtable_tests 0.3.1 -repair {130 drop_all_tables 131 execsql { CREATE TABLE t2(x PRIMARY KEY) }132} {133 1.1 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))" {}134 1.2 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))" {}135 1.3 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)" {}136 137 2.1 "CREATE TABLE t1(c1, c2, UNIQUE(c1))" {}138 2.2 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))" {}139 2.3 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)" {}140 141 3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {}142 143 4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {}144}145 146# -- syntax diagram column-def147#148do_createtable_tests 0.4.1 -repair {149 drop_all_tables 150} {151 1 {CREATE TABLE t1(152 col1,153 col2 TEXT,154 col3 INTEGER UNIQUE,155 col4 VARCHAR(10, 10) PRIMARY KEY,156 "name with spaces" REFERENCES t1157 );158 } {}159}160 161# -- syntax diagram create-table-stmt162#163do_createtable_tests 0.5.1 -repair {164 drop_all_tables 165 execsql { CREATE TABLE t2(a, b, c) }166} {167 1 "CREATE TABLE t1(a, b, c)" {}168 2 "CREATE TEMP TABLE t1(a, b, c)" {}169 3 "CREATE TEMPORARY TABLE t1(a, b, c)" {}170 4 "CREATE TABLE IF NOT EXISTS t1(a, b, c)" {}171 5 "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)" {}172 6 "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)" {}173 174 7 "CREATE TABLE main.t1(a, b, c)" {}175 8 "CREATE TEMP TABLE temp.t1(a, b, c)" {}176 9 "CREATE TEMPORARY TABLE temp.t1(a, b, c)" {}177 10 "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)" {}178 11 "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)" {}179 12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {}180 181 13 "CREATE TABLE t1 AS SELECT * FROM t2" {}182 14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {}183 15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {}184}185 186#187# 1: Explicit parent-key columns.188# 2: Implicit child-key columns.189#190# 1: MATCH FULL191# 2: MATCH PARTIAL192# 3: MATCH SIMPLE193# 4: MATCH STICK194# 5: 195#196# 1: ON DELETE SET NULL197# 2: ON DELETE SET DEFAULT198# 3: ON DELETE CASCADE199# 4: ON DELETE RESTRICT200# 5: ON DELETE NO ACTION201# 6:202#203# 1: ON UPDATE SET NULL204# 2: ON UPDATE SET DEFAULT205# 3: ON UPDATE CASCADE206# 4: ON UPDATE RESTRICT207# 5: ON UPDATE NO ACTION208# 6:209#210# 1: NOT DEFERRABLE INITIALLY DEFERRED211# 2: NOT DEFERRABLE INITIALLY IMMEDIATE212# 3: NOT DEFERRABLE213# 4: DEFERRABLE INITIALLY DEFERRED214# 5: DEFERRABLE INITIALLY IMMEDIATE215# 6: DEFERRABLE216# 7: 217#218do_createtable_tests 0.6.1 -repair {219 drop_all_tables 220 execsql { CREATE TABLE t2(x PRIMARY KEY, y) }221 execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }222} {223 11146 { CREATE TABLE t1(a 224 REFERENCES t2(x) MATCH FULL 225 ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE226 )} {}227 11412 { CREATE TABLE t1(a 228 REFERENCES t2(x) 229 ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL 230 NOT DEFERRABLE INITIALLY IMMEDIATE231 )} {}232 12135 { CREATE TABLE t1(a 233 REFERENCES t2(x) MATCH PARTIAL 234 ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE235 )} {}236 12427 { CREATE TABLE t1(a 237 REFERENCES t2(x) MATCH PARTIAL 238 ON DELETE RESTRICT ON UPDATE SET DEFAULT 239 )} {}240 12446 { CREATE TABLE t1(a 241 REFERENCES t2(x) MATCH PARTIAL 242 ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE243 )} {}244 12522 { CREATE TABLE t1(a 245 REFERENCES t2(x) MATCH PARTIAL 246 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE247 )} {}248 13133 { CREATE TABLE t1(a 249 REFERENCES t2(x) MATCH SIMPLE 250 ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE251 )} {}252 13216 { CREATE TABLE t1(a 253 REFERENCES t2(x) MATCH SIMPLE 254 ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE255 )} {}256 13263 { CREATE TABLE t1(a 257 REFERENCES t2(x) MATCH SIMPLE 258 ON DELETE SET DEFAULT NOT DEFERRABLE259 )} {}260 13421 { CREATE TABLE t1(a 261 REFERENCES t2(x) MATCH SIMPLE 262 ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED263 )} {}264 13432 { CREATE TABLE t1(a 265 REFERENCES t2(x) MATCH SIMPLE 266 ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE267 )} {}268 13523 { CREATE TABLE t1(a 269 REFERENCES t2(x) MATCH SIMPLE 270 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE271 )} {}272 14336 { CREATE TABLE t1(a 273 REFERENCES t2(x) MATCH STICK 274 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE275 )} {}276 14611 { CREATE TABLE t1(a 277 REFERENCES t2(x) MATCH STICK 278 ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED279 )} {}280 15155 { CREATE TABLE t1(a 281 REFERENCES t2(x)282 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE283 )} {}284 15453 { CREATE TABLE t1(a 285 REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE286 )} {}287 15661 { CREATE TABLE t1(a 288 REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED289 )} {}290 21115 { CREATE TABLE t1(a 291 REFERENCES t2 MATCH FULL 292 ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE293 )} {}294 21123 { CREATE TABLE t1(a 295 REFERENCES t2 MATCH FULL 296 ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE297 )} {}298 21217 { CREATE TABLE t1(a 299 REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL 300 )} {}301 21362 { CREATE TABLE t1(a 302 REFERENCES t2 MATCH FULL 303 ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE304 )} {}305 22143 { CREATE TABLE t1(a 306 REFERENCES t2 MATCH PARTIAL 307 ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE308 )} {}309 22156 { CREATE TABLE t1(a 310 REFERENCES t2 MATCH PARTIAL 311 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE312 )} {}313 22327 { CREATE TABLE t1(a 314 REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT 315 )} {}316 22663 { CREATE TABLE t1(a 317 REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE318 )} {}319 23236 { CREATE TABLE t1(a 320 REFERENCES t2 MATCH SIMPLE 321 ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE322 )} {}323 24155 { CREATE TABLE t1(a 324 REFERENCES t2 MATCH STICK 325 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE326 )} {}327 24522 { CREATE TABLE t1(a 328 REFERENCES t2 MATCH STICK 329 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE330 )} {}331 24625 { CREATE TABLE t1(a 332 REFERENCES t2 MATCH STICK 333 ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE334 )} {}335 25454 { CREATE TABLE t1(a 336 REFERENCES t2 337 ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED338 )} {}339}340 341#-------------------------------------------------------------------------342# Test cases e_createtable-1.* - test statements related to table and343# database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS344# clause.345#346drop_all_tables347forcedelete test.db2 test.db3348 349do_execsql_test e_createtable-1.0 {350 ATTACH 'test.db2' AS auxa;351 ATTACH 'test.db3' AS auxb;352} {}353 354# EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are355# reserved for internal use. It is an error to attempt to create a table356# with a name that starts with "sqlite_".357#358do_createtable_tests 1.1.1 -error {359 object name reserved for internal use: %s360} {361 1 "CREATE TABLE sqlite_abc(a, b, c)" sqlite_abc362 2 "CREATE TABLE temp.sqlite_helloworld(x)" sqlite_helloworld363 3 {CREATE TABLE auxa."sqlite__"(x, y)} sqlite__364 4 {CREATE TABLE auxb."sqlite_"(z)} sqlite_365 5 {CREATE TABLE "SQLITE_TBL"(z)} SQLITE_TBL366}367do_createtable_tests 1.1.2 {368 1 "CREATE TABLE sqlit_abc(a, b, c)" {}369 2 "CREATE TABLE temp.sqlitehelloworld(x)" {}370 3 {CREATE TABLE auxa."sqlite"(x, y)} {}371 4 {CREATE TABLE auxb."sqlite-"(z)} {}372 5 {CREATE TABLE "SQLITE-TBL"(z)} {}373}374 375 376# EVIDENCE-OF: R-18448-33677 If a schema-name is specified, it must be377# either "main", "temp", or the name of an attached database.378#379# EVIDENCE-OF: R-39822-07822 In this case the new table is created in380# the named database.381#382# Test cases 1.2.* test the first of the two requirements above. The383# second is verified by cases 1.3.*.384#385do_createtable_tests 1.2.1 -error {386 unknown database %s387} {388 1 "CREATE TABLE george.t1(a, b)" george389 2 "CREATE TABLE _.t1(a, b)" _390}391do_createtable_tests 1.2.2 {392 1 "CREATE TABLE main.abc(a, b, c)" {}393 2 "CREATE TABLE temp.helloworld(x)" {}394 3 {CREATE TABLE auxa."t 1"(x, y)} {}395 4 {CREATE TABLE auxb.xyz(z)} {}396}397drop_all_tables398if {[permutation]!="maindbname"} {399 do_createtable_tests 1.3 -tclquery {400 unset -nocomplain X401 array set X [table_list]402 list $X(main) $X(temp) $X(auxa) $X(auxb)403 } {404 1 "CREATE TABLE main.abc(a, b, c)" {abc {} {} {}}405 2 "CREATE TABLE main.t1(a, b, c)" {{abc t1} {} {} {}}406 3 "CREATE TABLE temp.tmp(a, b, c)" {{abc t1} tmp {} {}}407 4 "CREATE TABLE auxb.tbl(x, y)" {{abc t1} tmp {} tbl}408 5 "CREATE TABLE auxb.t1(k, v)" {{abc t1} tmp {} {t1 tbl}}409 6 "CREATE TABLE auxa.next(c, d)" {{abc t1} tmp next {t1 tbl}}410 }411}412 413# EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs414# between the "CREATE" and "TABLE" then the new table is created in the415# temp database.416#417drop_all_tables418if {[permutation]!="maindbname"} {419 do_createtable_tests 1.4 -tclquery {420 unset -nocomplain X421 array set X [table_list]422 list $X(main) $X(temp) $X(auxa) $X(auxb)423 } {424 1 "CREATE TEMP TABLE t1(a, b)" {{} t1 {} {}}425 2 "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}}426 }427}428 429# EVIDENCE-OF: R-23976-43329 It is an error to specify both a430# schema-name and the TEMP or TEMPORARY keyword, unless the schema-name431# is "temp".432#433drop_all_tables434do_createtable_tests 1.5.1 -error {435 temporary table name must be unqualified436} {437 1 "CREATE TEMP TABLE main.t1(a, b)" {}438 2 "CREATE TEMPORARY TABLE auxa.t2(a, b)" {}439 3 "CREATE TEMP TABLE auxb.t3(a, b)" {}440 4 "CREATE TEMPORARY TABLE main.xxx(x)" {}441}442drop_all_tables443if {[permutation]!="maindbname"} {444 do_createtable_tests 1.5.2 -tclquery {445 unset -nocomplain X446 array set X [table_list]447 list $X(main) $X(temp) $X(auxa) $X(auxb)448 } {449 1 "CREATE TEMP TABLE temp.t1(a, b)" {{} t1 {} {}}450 2 "CREATE TEMPORARY TABLE temp.t2(a, b)" {{} {t1 t2} {} {}}451 3 "CREATE TEMP TABLE TEMP.t3(a, b)" {{} {t1 t2 t3} {} {}}452 4 "CREATE TEMPORARY TABLE TEMP.xxx(x)" {{} {t1 t2 t3 xxx} {} {}}453 }454}455 456# EVIDENCE-OF: R-31997-24564 If no schema name is specified and the TEMP457# keyword is not present then the table is created in the main database.458#459drop_all_tables460if {[permutation]!="maindbname"} {461 do_createtable_tests 1.6 -tclquery {462 unset -nocomplain X463 array set X [table_list]464 list $X(main) $X(temp) $X(auxa) $X(auxb)465 } {466 1 "CREATE TABLE t1(a, b)" {t1 {} {} {}}467 2 "CREATE TABLE t2(a, b)" {{t1 t2} {} {} {}}468 3 "CREATE TABLE t3(a, b)" {{t1 t2 t3} {} {} {}}469 4 "CREATE TABLE xxx(x)" {{t1 t2 t3 xxx} {} {} {}}470 }471}472 473drop_all_tables474do_execsql_test e_createtable-1.7.0 {475 CREATE TABLE t1(x, y);476 CREATE INDEX i1 ON t1(x);477 CREATE VIEW v1 AS SELECT * FROM t1;478 479 CREATE TABLE auxa.tbl1(x, y);480 CREATE INDEX auxa.idx1 ON tbl1(x);481 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;482} {}483 484# EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create485# a new table in a database that already contains a table, index or view486# of the same name.487#488# Test cases 1.7.1.* verify that creating a table in a database with a489# table/index/view of the same name does fail. 1.7.2.* tests that creating490# a table with the same name as a table/index/view in a different database491# is Ok.492#493do_createtable_tests 1.7.1 -error { %s } {494 1 "CREATE TABLE t1(a, b)" {{table t1 already exists}}495 2 "CREATE TABLE i1(a, b)" {{there is already an index named i1}}496 3 "CREATE TABLE v1(a, b)" {{view v1 already exists}}497 4 "CREATE TABLE auxa.tbl1(a, b)" {{table tbl1 already exists}}498 5 "CREATE TABLE auxa.idx1(a, b)" {{there is already an index named idx1}}499 6 "CREATE TABLE auxa.view1(a, b)" {{view view1 already exists}}500}501do_createtable_tests 1.7.2 {502 1 "CREATE TABLE auxa.t1(a, b)" {}503 2 "CREATE TABLE auxa.i1(a, b)" {}504 3 "CREATE TABLE auxa.v1(a, b)" {}505 4 "CREATE TABLE tbl1(a, b)" {}506 5 "CREATE TABLE idx1(a, b)" {}507 6 "CREATE TABLE view1(a, b)" {}508}509 510# EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is511# specified as part of the CREATE TABLE statement and a table or view of512# the same name already exists, the CREATE TABLE command simply has no513# effect (and no error message is returned).514#515drop_all_tables516do_execsql_test e_createtable-1.8.0 {517 CREATE TABLE t1(x, y);518 CREATE INDEX i1 ON t1(x);519 CREATE VIEW v1 AS SELECT * FROM t1;520 CREATE TABLE auxa.tbl1(x, y);521 CREATE INDEX auxa.idx1 ON tbl1(x);522 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;523} {}524do_createtable_tests 1.8 {525 1 "CREATE TABLE IF NOT EXISTS t1(a, b)" {}526 2 "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)" {}527 3 "CREATE TABLE IF NOT EXISTS v1(a, b)" {}528 4 "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)" {}529}530 531# EVIDENCE-OF: R-16465-40078 An error is still returned if the table532# cannot be created because of an existing index, even if the "IF NOT533# EXISTS" clause is specified.534#535do_createtable_tests 1.9 -error { %s } {536 1 "CREATE TABLE IF NOT EXISTS i1(a, b)" 537 {{there is already an index named i1}}538 2 "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)" 539 {{there is already an index named idx1}}540}541 542# EVIDENCE-OF: R-05513-33819 It is not an error to create a table that543# has the same name as an existing trigger.544#545drop_all_tables546do_execsql_test e_createtable-1.10.0 {547 CREATE TABLE t1(x, y);548 CREATE TABLE auxb.t2(x, y);549 550 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN551 SELECT 1;552 END;553 CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN554 SELECT 1;555 END;556} {}557do_createtable_tests 1.10 {558 1 "CREATE TABLE tr1(a, b)" {}559 2 "CREATE TABLE tr2(a, b)" {}560 3 "CREATE TABLE auxb.tr1(a, b)" {}561 4 "CREATE TABLE auxb.tr2(a, b)" {}562}563 564# EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE565# statement.566#567drop_all_tables568do_execsql_test e_createtable-1.11.0 {569 CREATE TABLE t1(a, b);570 CREATE TABLE t2(a, b);571 CREATE TABLE auxa.t3(a, b);572 CREATE TABLE auxa.t4(a, b);573} {}574 575do_execsql_test e_createtable-1.11.1.1 {576 SELECT * FROM t1;577 SELECT * FROM t2;578 SELECT * FROM t3;579 SELECT * FROM t4;580} {}581do_execsql_test e_createtable-1.11.1.2 { DROP TABLE t1 } {}582do_catchsql_test e_createtable-1.11.1.3 { 583 SELECT * FROM t1 584} {1 {no such table: t1}}585do_execsql_test e_createtable-1.11.1.4 { DROP TABLE t3 } {}586do_catchsql_test e_createtable-1.11.1.5 { 587 SELECT * FROM t3 588} {1 {no such table: t3}}589 590do_execsql_test e_createtable-1.11.2.1 {591 SELECT name FROM sqlite_master;592 SELECT name FROM auxa.sqlite_master;593} {t2 t4}594do_execsql_test e_createtable-1.11.2.2 { DROP TABLE t2 } {}595do_execsql_test e_createtable-1.11.2.3 { DROP TABLE t4 } {}596do_execsql_test e_createtable-1.11.2.4 {597 SELECT name FROM sqlite_master;598 SELECT name FROM auxa.sqlite_master;599} {}600 601#-------------------------------------------------------------------------602# Test cases e_createtable-2.* - test statements related to the CREATE603# TABLE AS ... SELECT statement.604#605 606# Three Tcl commands:607#608# select_column_names SQL609# The argument must be a SELECT statement. Return a list of the names610# of the columns of the result-set that would be returned by executing611# the SELECT.612#613# table_column_names TBL614# The argument must be a table name. Return a list of column names, from615# left to right, for the table.616#617# table_column_decltypes TBL618# The argument must be a table name. Return a list of column declared619# types, from left to right, for the table.620#621proc sci {select cmd} {622 set res [list]623 set STMT [sqlite3_prepare_v2 db $select -1 dummy]624 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {625 lappend res [$cmd $STMT $i]626 }627 sqlite3_finalize $STMT628 set res629}630proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }631proc select_column_names {sql} { sci $sql sqlite3_column_name }632proc table_column_names {tbl} { tci $tbl sqlite3_column_name }633proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }634 635# Create a database schema. This schema is used by tests 2.1.* through 2.3.*.636#637drop_all_tables638do_execsql_test e_createtable-2.0 {639 CREATE TABLE t1(a, b, c);640 CREATE TABLE t2(d, e, f);641 CREATE TABLE t3(g BIGINT, h VARCHAR(10));642 CREATE TABLE t4(i BLOB, j ANYOLDATA);643 CREATE TABLE t5(k FLOAT, l INTEGER);644 CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));645 CREATE TABLE t7(x INTEGER PRIMARY KEY);646 CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');647 CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);648} {}649 650# EVIDENCE-OF: R-64828-59568 The table has the same number of columns as651# the rows returned by the SELECT statement. The name of each column is652# the same as the name of the corresponding column in the result set of653# the SELECT statement.654#655do_createtable_tests 2.1 -tclquery {656 table_column_names x1657} -repair {658 catchsql { DROP TABLE x1 }659} {660 1 "CREATE TABLE x1 AS SELECT * FROM t1" {a b c}661 2 "CREATE TABLE x1 AS SELECT c, b, a FROM t1" {c b a}662 3 "CREATE TABLE x1 AS SELECT * FROM t1, t2" {a b c d e f}663 4 "CREATE TABLE x1 AS SELECT count(*) FROM t1" {count(*)}664 5 "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}665}666 667# EVIDENCE-OF: R-55407-45319 The declared type of each column is668# determined by the expression affinity of the corresponding expression669# in the result set of the SELECT statement, as follows: Expression670# Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"671# REAL "REAL" BLOB (a.k.a "NONE") "" (empty string)672#673do_createtable_tests 2.2 -tclquery {674 table_column_decltypes x1675} -repair {676 catchsql { DROP TABLE x1 }677} {678 1 "CREATE TABLE x1 AS SELECT a FROM t1" {""}679 2 "CREATE TABLE x1 AS SELECT * FROM t3" {INT TEXT}680 3 "CREATE TABLE x1 AS SELECT * FROM t4" {"" NUM}681 4 "CREATE TABLE x1 AS SELECT * FROM t5" {REAL INT}682}683 684# EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has685# no PRIMARY KEY and no constraints of any kind. The default value of686# each column is NULL. The default collation sequence for each column of687# the new table is BINARY.688#689# The following tests create tables based on SELECT statements that read690# from tables that have primary keys, constraints and explicit default 691# collation sequences. None of this is transfered to the definition of692# the new table as stored in the sqlite_master table.693#694# Tests 2.3.2.* show that the default value of each column is NULL.695#696do_createtable_tests 2.3.1 -query {697 SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1698} {699 1 "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}700 2 "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}701 3 "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}702 4 "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}703}704do_execsql_test e_createtable-2.3.2.1 {705 INSERT INTO x1 DEFAULT VALUES;706 INSERT INTO x2 DEFAULT VALUES;707 INSERT INTO x3 DEFAULT VALUES;708 INSERT INTO x4 DEFAULT VALUES;709} {}710db nullvalue null711do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}712do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}713do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}714do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}715db nullvalue {}716 717drop_all_tables718do_execsql_test e_createtable-2.4.0 {719 CREATE TABLE t1(x, y);720 INSERT INTO t1 VALUES('i', 'one');721 INSERT INTO t1 VALUES('ii', 'two');722 INSERT INTO t1 VALUES('iii', 'three');723} {}724 725# EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are726# initially populated with the rows of data returned by the SELECT727# statement.728#729# EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending730# rowid values, starting with 1, in the order that they are returned by731# the SELECT statement.732#733# Each test case below is specified as the name of a table to create734# using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in735# creating it. The table is created. 736#737# Test cases 2.4.*.1 check that after it has been created, the data in the738# table is the same as the data returned by the SELECT statement executed as739# a standalone command, verifying the first testable statement above.740#741# Test cases 2.4.*.2 check that the rowids were allocated contiguously742# as required by the second testable statement above. That the rowids743# from the contiguous block were allocated to rows in the order rows are744# returned by the SELECT statement is verified by 2.4.*.1.745#746# EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement747# creates and populates a database table based on the results of a748# SELECT statement.749#750# The above is also considered to be tested by the following. It is751# clear that tables are being created and populated by the command in752# question.753#754foreach {tn tbl select} {755 1 x1 "SELECT * FROM t1"756 2 x2 "SELECT * FROM t1 ORDER BY x DESC"757 3 x3 "SELECT * FROM t1 ORDER BY x ASC"758} {759 # Create the table using a "CREATE TABLE ... AS SELECT ..." command.760 execsql [subst {CREATE TABLE $tbl AS $select}]761 762 # Check that the rows inserted into the table, sorted in ascending rowid763 # order, match those returned by executing the SELECT statement as a764 # standalone command.765 do_execsql_test e_createtable-2.4.$tn.1 [subst {766 SELECT * FROM $tbl ORDER BY rowid;767 }] [execsql $select]768 769 # Check that the rowids in the new table are a contiguous block starting770 # with rowid 1. Note that this will fail if SELECT statement $select 771 # returns 0 rows (as max(rowid) will be NULL).772 do_execsql_test e_createtable-2.4.$tn.2 [subst {773 SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl774 }] {1 1}775}776 777#--------------------------------------------------------------------------778# Test cases for column defintions in CREATE TABLE statements that do not779# use a SELECT statement. Not including data constraints. In other words,780# tests for the specification of:781#782# * declared types,783# * default values, and784# * default collation sequences.785#786 787# EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not788# restrict the type of data that may be inserted into a column based on789# the columns declared type.790#791# Test this by creating a few tables with varied declared types, then792# inserting various different types of values into them.793#794drop_all_tables795do_execsql_test e_createtable-3.1.0 {796 CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);797 CREATE TABLE t2(a DATETIME, b STRING, c REAL);798 CREATE TABLE t3(o, t);799} {}800 801# value type -> declared column type802# ----------------------------------803# integer -> VARCHAR(10)804# string -> INTEGER805# blob -> DOUBLE806#807do_execsql_test e_createtable-3.1.1 {808 INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');809 SELECT * FROM t1;810} {14 {quite a lengthy string} UVU}811 812# string -> DATETIME813# integer -> STRING814# time -> REAL815#816do_execsql_test e_createtable-3.1.2 {817 INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');818 SELECT * FROM t2;819} {{not a datetime} 13 12:41:59}820 821# EVIDENCE-OF: R-10565-09557 The declared type of a column is used to822# determine the affinity of the column only.823#824# Affinities are tested in more detail elsewhere (see document825# datatype3.html). Here, just test that affinity transformations826# consistent with the expected affinity of each column (based on827# the declared type) appear to take place.828#829# Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL830# Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL831# Affinities of t3 (test cases 3.2.3.*): NONE, NONE832#833do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {}834 835do_createtable_tests 3.2.1 -query {836 SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1;837} {838 1 "INSERT INTO t1 VALUES(15, '22.0', '14')" {'15' 22 14.0}839 2 "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)" {'22.0' 22 22.0}840}841do_createtable_tests 3.2.2 -query {842 SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1;843} {844 1 "INSERT INTO t2 VALUES(15, '22.0', '14')" {15 22 14.0}845 2 "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)" {22 22 22.0}846}847do_createtable_tests 3.2.3 -query {848 SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1;849} {850 1 "INSERT INTO t3 VALUES('15', '22.0')" {'15' '22.0'}851 2 "INSERT INTO t3 VALUES(15, 22.0)" {15 22.0}852}853 854# EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause855# attached to a column definition, then the default value of the column856# is NULL.857#858# None of the columns in table t1 have an explicit DEFAULT clause.859# So testing that the default value of all columns in table t1 is860# NULL serves to verify the above.861# 862do_createtable_tests 3.2.3 -query {863 SELECT quote(x), quote(y), quote(z) FROM t1864} -repair {865 execsql { DELETE FROM t1 }866} {867 1 "INSERT INTO t1(x, y) VALUES('abc', 'xyz')" {'abc' 'xyz' NULL}868 2 "INSERT INTO t1(x, z) VALUES('abc', 'xyz')" {'abc' NULL 'xyz'}869 3 "INSERT INTO t1 DEFAULT VALUES" {NULL NULL NULL}870}871 872# EVIDENCE-OF: R-07343-35026 An explicit DEFAULT clause may specify that873# the default value is NULL, a string constant, a blob constant, a874# signed-number, or any constant expression enclosed in parentheses. A875# default value may also be one of the special case-independent keywords876# CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.877#878do_execsql_test e_createtable-3.3.1 {879 CREATE TABLE t4(880 a DEFAULT NULL,881 b DEFAULT 'string constant',882 c DEFAULT X'424C4F42',883 d DEFAULT 1,884 e DEFAULT -1,885 f DEFAULT 3.14,886 g DEFAULT -3.14,887 h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),888 i DEFAULT CURRENT_TIME,889 j DEFAULT CURRENT_DATE,890 k DEFAULT CURRENT_TIMESTAMP891 );892} {}893 894# EVIDENCE-OF: R-33440-07331 For the purposes of the DEFAULT clause, an895# expression is considered constant if it contains no sub-queries,896# column or table references, bound parameters, or string literals897# enclosed in double-quotes instead of single-quotes.898#899do_createtable_tests 3.4.1 -error {900 default value of column [x] is not constant901} {902 1 {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))} {}903 2 {CREATE TABLE t5(x DEFAULT ( "abc" ))} {}904 3 {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))} {}905 4 {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))} {}906 5 {CREATE TABLE t5(x DEFAULT ( x!=?1 ))} {}907}908do_createtable_tests 3.4.2 -repair {909 catchsql { DROP TABLE t5 }910} {911 1 {CREATE TABLE t5(x DEFAULT ( 'abc' ))} {}912 2 {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))} {}913}914 915# EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table916# by an INSERT statement that does not provide explicit values for all917# table columns the values stored in the new row are determined by their918# default values919#920# Verify this with some assert statements for which all, some and no921# columns lack explicit values.922#923set sqlite_current_time 1000000000924do_createtable_tests 3.5 -query {925 SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), 926 quote(g), quote(h), quote(i), quote(j), quote(k)927 FROM t4 ORDER BY rowid DESC LIMIT 1;928} {929 1 "INSERT INTO t4 DEFAULT VALUES" {930 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 931 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}932 }933 934 2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {935 1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}936 }937 938 3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {939 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1940 }941 942 4 "INSERT INTO t4(a,b,c,d,e,f,g,h,i,j,k) VALUES(1,2,3,4,5,6,7,8,9,10,11)" {943 1 2 3 4 5 6 7 8 9 10 11944 }945}946 947# EVIDENCE-OF: R-12572-62501 If the default value of the column is a948# constant NULL, text, blob or signed-number value, then that value is949# used directly in the new row.950#951do_execsql_test e_createtable-3.6.1 {952 CREATE TABLE t5(953 a DEFAULT NULL, 954 b DEFAULT 'text value', 955 c DEFAULT X'424C4F42',956 d DEFAULT -45678.6,957 e DEFAULT 394507958 );959} {}960do_execsql_test e_createtable-3.6.2 {961 INSERT INTO t5 DEFAULT VALUES;962 SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;963} {NULL {'text value'} X'424C4F42' -45678.6 394507}964 965# EVIDENCE-OF: R-60616-50251 If the default value of a column is an966# expression in parentheses, then the expression is evaluated once for967# each row inserted and the results used in the new row.968#969# Test case 3.6.4 demonstrates that the expression is evaluated 970# separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."971# command.972#973set ::nextint 0974proc nextint {} { incr ::nextint }975db func nextint nextint976 977do_execsql_test e_createtable-3.7.1 {978 CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));979} {}980do_execsql_test e_createtable-3.7.2 {981 INSERT INTO t6 DEFAULT VALUES;982 SELECT quote(a), quote(b) FROM t6;983} {1 2}984do_execsql_test e_createtable-3.7.3 {985 INSERT INTO t6(a) VALUES('X');986 SELECT quote(a), quote(b) FROM t6;987} {1 2 'X' 3}988do_execsql_test e_createtable-3.7.4 {989 INSERT INTO t6(a) SELECT a FROM t6;990 SELECT quote(a), quote(b) FROM t6;991} {1 2 'X' 3 1 4 'X' 5}992 993# EVIDENCE-OF: R-15363-55230 If the default value of a column is994# CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used995# in the new row is a text representation of the current UTC date and/or996# time.997#998# This is difficult to test literally without knowing what time the 999# user will run the tests. Instead, we test that the three cases1000# above set the value to the current date and/or time according to1001# the xCurrentTime() method of the VFS. Which is usually the same1002# as UTC. In this case, however, we instrument it to always return1003# a time equivalent to "2001-09-09 01:46:40 UTC".1004#1005set sqlite_current_time 10000000001006do_execsql_test e_createtable-3.8.1 {1007 CREATE TABLE t7(1008 a DEFAULT CURRENT_TIME, 1009 b DEFAULT CURRENT_DATE, 1010 c DEFAULT CURRENT_TIMESTAMP1011 );1012} {}1013do_execsql_test e_createtable-3.8.2 {1014 INSERT INTO t7 DEFAULT VALUES;1015 SELECT quote(a), quote(b), quote(c) FROM t7;1016} {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}}1017 1018 1019# EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value1020# is "HH:MM:SS".1021#1022# EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".1023#1024# EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is1025# "YYYY-MM-DD HH:MM:SS".1026#1027# The three above are demonstrated by tests 1, 2 and 3 below. 1028# Respectively.1029#1030do_createtable_tests 3.8.3 -query {1031 SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1;1032} {1033 1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y}1034 2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x}1035 3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}}1036}1037 1038# EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a1039# collating sequence to use as the default collation sequence for the1040# column.1041#1042# EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the1043# default collation sequence is BINARY.1044#1045do_execsql_test e_createtable-3-9.1 {1046 CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);1047 INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc');1048 INSERT INTO t8 VALUES('abc ', 'abc ', 'abc ', 'abc ');1049 INSERT INTO t8 VALUES('ABC ', 'ABC ', 'ABC ', 'ABC ');1050 INSERT INTO t8 VALUES('ABC', 'ABC', 'ABC', 'ABC');1051} {}1052do_createtable_tests 3.9 {1053 2 "SELECT a FROM t8 ORDER BY a, rowid" {abc ABC {abc } {ABC }}1054 3 "SELECT b FROM t8 ORDER BY b, rowid" {{ABC } ABC abc {abc }}1055 4 "SELECT c FROM t8 ORDER BY c, rowid" {ABC {ABC } abc {abc }}1056 5 "SELECT d FROM t8 ORDER BY d, rowid" {ABC {ABC } abc {abc }}1057}1058 1059# EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited1060# by the SQLITE_MAX_COLUMN compile-time parameter.1061#1062proc columns {n} {1063 set res [list]1064 for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }1065 join $res ", "1066}1067do_execsql_test e_createtable-3.10.1 [subst {1068 CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);1069}] {}1070do_catchsql_test e_createtable-3.10.2 [subst {1071 CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);1072}] {1 {too many columns on t10}}1073 1074# EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at1075# runtime using the sqlite3_limit() C/C++ interface.1076#1077# A 30,000 byte blob consumes 30,003 bytes of record space. A record 1078# that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests1079# 3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered1080# at runtime, are based on this calculation.1081#1082sqlite3_limit db SQLITE_LIMIT_COLUMN 5001083do_execsql_test e_createtable-3.11.1 [subst {1084 CREATE TABLE t10([columns 500]);1085}] {}1086do_catchsql_test e_createtable-3.11.2 [subst {1087 CREATE TABLE t11([columns 501]);1088}] {1 {too many columns on t11}}1089 1090# Check that it is not possible to raise the column limit above its 1091# default compile time value.1092#1093sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]1094do_catchsql_test e_createtable-3.11.3 [subst {1095 CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);1096}] {1 {too many columns on t11}}1097 1098sqlite3_limit db SQLITE_LIMIT_LENGTH 900101099do_execsql_test e_createtable-3.11.4 {1100 CREATE TABLE t12(a, b, c);1101 INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));1102} {}1103do_catchsql_test e_createtable-3.11.5 {1104 INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));1105} {1 {string or blob too big}}1106 1107#-------------------------------------------------------------------------1108# Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT 1109# NULL and CHECK constraints).1110#1111 1112# EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one1113# PRIMARY KEY.1114# 1115# EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY1116# KEY clause appears in a CREATE TABLE statement.1117#1118# To test the two above, show that zero primary keys is Ok, one primary1119# key is Ok, and two or more primary keys is an error.1120#1121drop_all_tables1122do_createtable_tests 4.1.1 {1123 1 "CREATE TABLE t1(a, b, c)" {}1124 2 "CREATE TABLE t2(a PRIMARY KEY, b, c)" {}1125 3 "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))" {}1126 4 "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))" {}1127}1128do_createtable_tests 4.1.2 -error {1129 table "t5" has more than one primary key1130} {1131 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {}1132 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {}1133 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {}1134 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}1135 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {}1136 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {}1137}1138 1139# EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary1140# tables but is required for WITHOUT ROWID tables.1141#1142do_catchsql_test 4.1.3 {1143 CREATE TABLE t6(a, b); --ok1144} {0 {}}1145do_catchsql_test 4.1.4 {1146 CREATE TABLE t7(a, b) WITHOUT ROWID; --Error, no PRIMARY KEY1147} {1 {PRIMARY KEY missing on table t7}}1148 1149 1150proc table_pk {tbl} { 1151 set pk [list]1152 db eval "pragma table_info($tbl)" a {1153 if {$a(pk)} { lappend pk $a(name) }1154 }1155 set pk1156}1157 1158# EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a1159# column definition, then the primary key for the table consists of that1160# single column.1161#1162# The above is tested by 4.2.1.*1163#1164# EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as1165# a table-constraint, then the primary key of the table consists of the1166# list of columns specified as part of the PRIMARY KEY clause.1167#1168# The above is tested by 4.2.2.*1169#1170do_createtable_tests 4.2 -repair {1171 catchsql { DROP TABLE t5 }1172} -tclquery {1173 table_pk t51174} {1175 1.1 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}1176 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a}1177 1178 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a}1179 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c}1180 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}1181}1182 1183# EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must1184# have a unique combination of values in its primary key columns.1185#1186# EVIDENCE-OF: R-06471-16287 If an INSERT or UPDATE statement attempts1187# to modify the table content so that two or more rows have identical1188# primary key values, that is a constraint violation.1189#1190drop_all_tables1191do_execsql_test 4.3.0 {1192 CREATE TABLE t1(x PRIMARY KEY, y);1193 INSERT INTO t1 VALUES(0, 'zero');1194 INSERT INTO t1 VALUES(45.5, 'one');1195 INSERT INTO t1 VALUES('brambles', 'two');1196 INSERT INTO t1 VALUES(X'ABCDEF', 'three');1197 1198 CREATE TABLE t2(x, y, PRIMARY KEY(x, y));1199 INSERT INTO t2 VALUES(0, 'zero');1200 INSERT INTO t2 VALUES(45.5, 'one');