3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
12 # This file implements tests to verify that the "testable statements" in
13 # the lang_createtable.html document are correct.
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 set ::testprefix e_createtable
23 # e_createtable-0.*: Test that the syntax diagrams are correct.
25 # e_createtable-1.*: Test statements related to table and database names,
26 # the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
28 # e_createtable-2.*: Test "CREATE TABLE AS" statements.
31 proc do_createtable_tests {nm args} {
32 uplevel do_select_tests [list e_createtable-$nm] $args
36 #-------------------------------------------------------------------------
37 # This command returns a serialized tcl array mapping from the name of
38 # each attached database to a list of tables in that database. For example,
39 # if the database schema is created with:
42 # CREATE TEMP TABLE t2(x);
43 # CREATE TEMP TABLE t3(x);
45 # Then this command returns "main t1 temp {t2 t3}".
49 db eval { pragma database_list } a {
51 set master $a(name).sqlite_master
52 if {$dbname == "temp"} { set master sqlite_temp_master }
54 db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
61 # EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name
63 do_createtable_tests 0.1.1 -repair {
66 1 "CREATE TABLE t1(c1 one)" {}
67 2 "CREATE TABLE t1(c1 one two)" {}
68 3 "CREATE TABLE t1(c1 one two three)" {}
69 4 "CREATE TABLE t1(c1 one two three four)" {}
70 5 "CREATE TABLE t1(c1 one two three four(14))" {}
71 6 "CREATE TABLE t1(c1 one two three four(14, 22))" {}
72 7 "CREATE TABLE t1(c1 var(+14, -22.3))" {}
73 8 "CREATE TABLE t1(c1 var(1.0e10))" {}
75 do_createtable_tests 0.1.2 -error {
76 near "%s": syntax error
78 1 "CREATE TABLE t1(c1 one(number))" {number}
82 # EVIDENCE-OF: R-18762-12428 -- syntax diagram column-constraint
84 # Note: Not shown in the syntax diagram is the "NULL" constraint. This
85 # is the opposite of "NOT NULL" - it implies that the column may
86 # take a NULL value. This is the default anyway, so this type of
87 # constraint is rarely used.
89 do_createtable_tests 0.2.1 -repair {
91 execsql { CREATE TABLE t2(x PRIMARY KEY) }
93 1.1 "CREATE TABLE t1(c1 text PRIMARY KEY)" {}
94 1.2 "CREATE TABLE t1(c1 text PRIMARY KEY ASC)" {}
95 1.3 "CREATE TABLE t1(c1 text PRIMARY KEY DESC)" {}
96 1.4 "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)" {}
98 2.1 "CREATE TABLE t1(c1 text NOT NULL)" {}
99 2.2 "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)" {}
100 2.3 "CREATE TABLE t1(c1 text NULL)" {}
101 2.4 "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)" {}
103 3.1 "CREATE TABLE t1(c1 text UNIQUE)" {}
104 3.2 "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)" {}
106 4.1 "CREATE TABLE t1(c1 text CHECK(c1!=0))" {}
107 4.2 "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))" {}
109 5.1 "CREATE TABLE t1(c1 text DEFAULT 1)" {}
110 5.2 "CREATE TABLE t1(c1 text DEFAULT -1)" {}
111 5.3 "CREATE TABLE t1(c1 text DEFAULT +1)" {}
112 5.4 "CREATE TABLE t1(c1 text DEFAULT -45.8e22)" {}
113 5.5 "CREATE TABLE t1(c1 text DEFAULT (1+1))" {}
114 5.6 "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))" {}
116 6.1 "CREATE TABLE t1(c1 text COLLATE nocase)" {}
117 6.2 "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)" {}
119 7.1 "CREATE TABLE t1(c1 REFERENCES t2)" {}
120 7.2 "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)" {}
124 PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1
129 REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY
134 # EVIDENCE-OF: R-17905-31923 -- syntax diagram table-constraint
136 do_createtable_tests 0.3.1 -repair {
138 execsql { CREATE TABLE t2(x PRIMARY KEY) }
140 1.1 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))" {}
141 1.2 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))" {}
142 1.3 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)" {}
144 2.1 "CREATE TABLE t1(c1, c2, UNIQUE(c1))" {}
145 2.2 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))" {}
146 2.3 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)" {}
148 3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {}
150 4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {}
153 # EVIDENCE-OF: R-18765-31171 -- syntax diagram column-def
155 do_createtable_tests 0.4.1 -repair {
162 col4 VARCHAR(10, 10) PRIMARY KEY,
163 "name with spaces" REFERENCES t1
168 # EVIDENCE-OF: R-59573-11075 -- syntax diagram create-table-stmt
170 do_createtable_tests 0.5.1 -repair {
172 execsql { CREATE TABLE t2(a, b, c) }
174 1 "CREATE TABLE t1(a, b, c)" {}
175 2 "CREATE TEMP TABLE t1(a, b, c)" {}
176 3 "CREATE TEMPORARY TABLE t1(a, b, c)" {}
177 4 "CREATE TABLE IF NOT EXISTS t1(a, b, c)" {}
178 5 "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)" {}
179 6 "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)" {}
181 7 "CREATE TABLE main.t1(a, b, c)" {}
182 8 "CREATE TEMP TABLE temp.t1(a, b, c)" {}
183 9 "CREATE TEMPORARY TABLE temp.t1(a, b, c)" {}
184 10 "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)" {}
185 11 "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)" {}
186 12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {}
188 13 "CREATE TABLE t1 AS SELECT * FROM t2" {}
189 14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {}
190 15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {}
193 # EVIDENCE-OF: R-32138-02228 -- syntax diagram foreign-key-clause
195 # 1: Explicit parent-key columns.
196 # 2: Implicit child-key columns.
204 # 1: ON DELETE SET NULL
205 # 2: ON DELETE SET DEFAULT
206 # 3: ON DELETE CASCADE
207 # 4: ON DELETE RESTRICT
208 # 5: ON DELETE NO ACTION
211 # 1: ON UPDATE SET NULL
212 # 2: ON UPDATE SET DEFAULT
213 # 3: ON UPDATE CASCADE
214 # 4: ON UPDATE RESTRICT
215 # 5: ON UPDATE NO ACTION
218 # 1: NOT DEFERRABLE INITIALLY DEFERRED
219 # 2: NOT DEFERRABLE INITIALLY IMMEDIATE
221 # 4: DEFERRABLE INITIALLY DEFERRED
222 # 5: DEFERRABLE INITIALLY IMMEDIATE
226 do_createtable_tests 0.6.1 -repair {
228 execsql { CREATE TABLE t2(x PRIMARY KEY, y) }
229 execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }
231 11146 { CREATE TABLE t1(a
232 REFERENCES t2(x) MATCH FULL
233 ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE
235 11412 { CREATE TABLE t1(a
237 ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL
238 NOT DEFERRABLE INITIALLY IMMEDIATE
240 12135 { CREATE TABLE t1(a
241 REFERENCES t2(x) MATCH PARTIAL
242 ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE
244 12427 { CREATE TABLE t1(a
245 REFERENCES t2(x) MATCH PARTIAL
246 ON DELETE RESTRICT ON UPDATE SET DEFAULT
248 12446 { CREATE TABLE t1(a
249 REFERENCES t2(x) MATCH PARTIAL
250 ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE
252 12522 { CREATE TABLE t1(a
253 REFERENCES t2(x) MATCH PARTIAL
254 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
256 13133 { CREATE TABLE t1(a
257 REFERENCES t2(x) MATCH SIMPLE
258 ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE
260 13216 { CREATE TABLE t1(a
261 REFERENCES t2(x) MATCH SIMPLE
262 ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE
264 13263 { CREATE TABLE t1(a
265 REFERENCES t2(x) MATCH SIMPLE
266 ON DELETE SET DEFAULT NOT DEFERRABLE
268 13421 { CREATE TABLE t1(a
269 REFERENCES t2(x) MATCH SIMPLE
270 ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED
272 13432 { CREATE TABLE t1(a
273 REFERENCES t2(x) MATCH SIMPLE
274 ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
276 13523 { CREATE TABLE t1(a
277 REFERENCES t2(x) MATCH SIMPLE
278 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE
280 14336 { CREATE TABLE t1(a
281 REFERENCES t2(x) MATCH STICK
282 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE
284 14611 { CREATE TABLE t1(a
285 REFERENCES t2(x) MATCH STICK
286 ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED
288 15155 { CREATE TABLE t1(a
290 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
292 15453 { CREATE TABLE t1(a
293 REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE
295 15661 { CREATE TABLE t1(a
296 REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED
298 21115 { CREATE TABLE t1(a
299 REFERENCES t2 MATCH FULL
300 ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE
302 21123 { CREATE TABLE t1(a
303 REFERENCES t2 MATCH FULL
304 ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE
306 21217 { CREATE TABLE t1(a
307 REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL
309 21362 { CREATE TABLE t1(a
310 REFERENCES t2 MATCH FULL
311 ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
313 22143 { CREATE TABLE t1(a
314 REFERENCES t2 MATCH PARTIAL
315 ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE
317 22156 { CREATE TABLE t1(a
318 REFERENCES t2 MATCH PARTIAL
319 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE
321 22327 { CREATE TABLE t1(a
322 REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT
324 22663 { CREATE TABLE t1(a
325 REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE
327 23236 { CREATE TABLE t1(a
328 REFERENCES t2 MATCH SIMPLE
329 ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE
331 24155 { CREATE TABLE t1(a
332 REFERENCES t2 MATCH STICK
333 ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
335 24522 { CREATE TABLE t1(a
336 REFERENCES t2 MATCH STICK
337 ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
339 24625 { CREATE TABLE t1(a
340 REFERENCES t2 MATCH STICK
341 ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE
343 25454 { CREATE TABLE t1(a
345 ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED
349 #-------------------------------------------------------------------------
350 # Test cases e_createtable-1.* - test statements related to table and
351 # database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS
355 forcedelete test.db2 test.db3
357 do_execsql_test e_createtable-1.0 {
358 ATTACH 'test.db2' AS auxa;
359 ATTACH 'test.db3' AS auxb;
362 # EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are
363 # reserved for internal use. It is an error to attempt to create a table
364 # with a name that starts with "sqlite_".
366 do_createtable_tests 1.1.1 -error {
367 object name reserved for internal use: %s
369 1 "CREATE TABLE sqlite_abc(a, b, c)" sqlite_abc
370 2 "CREATE TABLE temp.sqlite_helloworld(x)" sqlite_helloworld
371 3 {CREATE TABLE auxa."sqlite__"(x, y)} sqlite__
372 4 {CREATE TABLE auxb."sqlite_"(z)} sqlite_
373 5 {CREATE TABLE "SQLITE_TBL"(z)} SQLITE_TBL
375 do_createtable_tests 1.1.2 {
376 1 "CREATE TABLE sqlit_abc(a, b, c)" {}
377 2 "CREATE TABLE temp.sqlitehelloworld(x)" {}
378 3 {CREATE TABLE auxa."sqlite"(x, y)} {}
379 4 {CREATE TABLE auxb."sqlite-"(z)} {}
380 5 {CREATE TABLE "SQLITE-TBL"(z)} {}
384 # EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it
385 # must be either "main", "temp", or the name of an attached database.
387 # EVIDENCE-OF: R-39822-07822 In this case the new table is created in
388 # the named database.
390 # Test cases 1.2.* test the first of the two requirements above. The
391 # second is verified by cases 1.3.*.
393 do_createtable_tests 1.2.1 -error {
396 1 "CREATE TABLE george.t1(a, b)" george
397 2 "CREATE TABLE _.t1(a, b)" _
399 do_createtable_tests 1.2.2 {
400 1 "CREATE TABLE main.abc(a, b, c)" {}
401 2 "CREATE TABLE temp.helloworld(x)" {}
402 3 {CREATE TABLE auxa."t 1"(x, y)} {}
403 4 {CREATE TABLE auxb.xyz(z)} {}
406 do_createtable_tests 1.3 -tclquery {
408 array set X [table_list]
409 list $X(main) $X(temp) $X(auxa) $X(auxb)
411 1 "CREATE TABLE main.abc(a, b, c)" {abc {} {} {}}
412 2 "CREATE TABLE main.t1(a, b, c)" {{abc t1} {} {} {}}
413 3 "CREATE TABLE temp.tmp(a, b, c)" {{abc t1} tmp {} {}}
414 4 "CREATE TABLE auxb.tbl(x, y)" {{abc t1} tmp {} tbl}
415 5 "CREATE TABLE auxb.t1(k, v)" {{abc t1} tmp {} {t1 tbl}}
416 6 "CREATE TABLE auxa.next(c, d)" {{abc t1} tmp next {t1 tbl}}
419 # EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs
420 # between the "CREATE" and "TABLE" then the new table is created in the
424 do_createtable_tests 1.4 -tclquery {
426 array set X [table_list]
427 list $X(main) $X(temp) $X(auxa) $X(auxb)
429 1 "CREATE TEMP TABLE t1(a, b)" {{} t1 {} {}}
430 2 "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}}
433 # EVIDENCE-OF: R-49439-47561 It is an error to specify both a
434 # <database-name> and the TEMP or TEMPORARY keyword, unless the
435 # <database-name> is "temp".
438 do_createtable_tests 1.5.1 -error {
439 temporary table name must be unqualified
441 1 "CREATE TEMP TABLE main.t1(a, b)" {}
442 2 "CREATE TEMPORARY TABLE auxa.t2(a, b)" {}
443 3 "CREATE TEMP TABLE auxb.t3(a, b)" {}
444 4 "CREATE TEMPORARY TABLE main.xxx(x)" {}
447 do_createtable_tests 1.5.2 -tclquery {
449 array set X [table_list]
450 list $X(main) $X(temp) $X(auxa) $X(auxb)
452 1 "CREATE TEMP TABLE temp.t1(a, b)" {{} t1 {} {}}
453 2 "CREATE TEMPORARY TABLE temp.t2(a, b)" {{} {t1 t2} {} {}}
454 3 "CREATE TEMP TABLE TEMP.t3(a, b)" {{} {t1 t2 t3} {} {}}
455 4 "CREATE TEMPORARY TABLE TEMP.xxx(x)" {{} {t1 t2 t3 xxx} {} {}}
458 # EVIDENCE-OF: R-00917-09393 If no database name is specified and the
459 # TEMP keyword is not present then the table is created in the main
463 do_createtable_tests 1.6 -tclquery {
465 array set X [table_list]
466 list $X(main) $X(temp) $X(auxa) $X(auxb)
468 1 "CREATE TABLE t1(a, b)" {t1 {} {} {}}
469 2 "CREATE TABLE t2(a, b)" {{t1 t2} {} {} {}}
470 3 "CREATE TABLE t3(a, b)" {{t1 t2 t3} {} {} {}}
471 4 "CREATE TABLE xxx(x)" {{t1 t2 t3 xxx} {} {} {}}
475 do_execsql_test e_createtable-1.7.0 {
476 CREATE TABLE t1(x, y);
477 CREATE INDEX i1 ON t1(x);
478 CREATE VIEW v1 AS SELECT * FROM t1;
480 CREATE TABLE auxa.tbl1(x, y);
481 CREATE INDEX auxa.idx1 ON tbl1(x);
482 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
485 # EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create
486 # a new table in a database that already contains a table, index or view
489 # Test cases 1.7.1.* verify that creating a table in a database with a
490 # table/index/view of the same name does fail. 1.7.2.* tests that creating
491 # a table with the same name as a table/index/view in a different database
494 do_createtable_tests 1.7.1 -error { %s } {
495 1 "CREATE TABLE t1(a, b)" {{table t1 already exists}}
496 2 "CREATE TABLE i1(a, b)" {{there is already an index named i1}}
497 3 "CREATE TABLE v1(a, b)" {{table v1 already exists}}
498 4 "CREATE TABLE auxa.tbl1(a, b)" {{table tbl1 already exists}}
499 5 "CREATE TABLE auxa.idx1(a, b)" {{there is already an index named idx1}}
500 6 "CREATE TABLE auxa.view1(a, b)" {{table view1 already exists}}
502 do_createtable_tests 1.7.2 {
503 1 "CREATE TABLE auxa.t1(a, b)" {}
504 2 "CREATE TABLE auxa.i1(a, b)" {}
505 3 "CREATE TABLE auxa.v1(a, b)" {}
506 4 "CREATE TABLE tbl1(a, b)" {}
507 5 "CREATE TABLE idx1(a, b)" {}
508 6 "CREATE TABLE view1(a, b)" {}
511 # EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is
512 # specified as part of the CREATE TABLE statement and a table or view of
513 # the same name already exists, the CREATE TABLE command simply has no
514 # effect (and no error message is returned).
517 do_execsql_test e_createtable-1.8.0 {
518 CREATE TABLE t1(x, y);
519 CREATE INDEX i1 ON t1(x);
520 CREATE VIEW v1 AS SELECT * FROM t1;
521 CREATE TABLE auxa.tbl1(x, y);
522 CREATE INDEX auxa.idx1 ON tbl1(x);
523 CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
525 do_createtable_tests 1.8 {
526 1 "CREATE TABLE IF NOT EXISTS t1(a, b)" {}
527 2 "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)" {}
528 3 "CREATE TABLE IF NOT EXISTS v1(a, b)" {}
529 4 "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)" {}
532 # EVIDENCE-OF: R-16465-40078 An error is still returned if the table
533 # cannot be created because of an existing index, even if the "IF NOT
534 # EXISTS" clause is specified.
536 do_createtable_tests 1.9 -error { %s } {
537 1 "CREATE TABLE IF NOT EXISTS i1(a, b)"
538 {{there is already an index named i1}}
539 2 "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)"
540 {{there is already an index named idx1}}
543 # EVIDENCE-OF: R-05513-33819 It is not an error to create a table that
544 # has the same name as an existing trigger.
547 do_execsql_test e_createtable-1.10.0 {
548 CREATE TABLE t1(x, y);
549 CREATE TABLE auxb.t2(x, y);
551 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
554 CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN
558 do_createtable_tests 1.10 {
559 1 "CREATE TABLE tr1(a, b)" {}
560 2 "CREATE TABLE tr2(a, b)" {}
561 3 "CREATE TABLE auxb.tr1(a, b)" {}
562 4 "CREATE TABLE auxb.tr2(a, b)" {}
565 # EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE
569 do_execsql_test e_createtable-1.11.0 {
570 CREATE TABLE t1(a, b);
571 CREATE TABLE t2(a, b);
572 CREATE TABLE auxa.t3(a, b);
573 CREATE TABLE auxa.t4(a, b);
576 do_execsql_test e_createtable-1.11.1.1 {
582 do_execsql_test e_createtable-1.11.1.2 { DROP TABLE t1 } {}
583 do_catchsql_test e_createtable-1.11.1.3 {
585 } {1 {no such table: t1}}
586 do_execsql_test e_createtable-1.11.1.4 { DROP TABLE t3 } {}
587 do_catchsql_test e_createtable-1.11.1.5 {
589 } {1 {no such table: t3}}
591 do_execsql_test e_createtable-1.11.2.1 {
592 SELECT name FROM sqlite_master;
593 SELECT name FROM auxa.sqlite_master;
595 do_execsql_test e_createtable-1.11.2.2 { DROP TABLE t2 } {}
596 do_execsql_test e_createtable-1.11.2.3 { DROP TABLE t4 } {}
597 do_execsql_test e_createtable-1.11.2.4 {
598 SELECT name FROM sqlite_master;
599 SELECT name FROM auxa.sqlite_master;
602 #-------------------------------------------------------------------------
603 # Test cases e_createtable-2.* - test statements related to the CREATE
604 # TABLE AS ... SELECT statement.
607 # Three Tcl commands:
609 # select_column_names SQL
610 # The argument must be a SELECT statement. Return a list of the names
611 # of the columns of the result-set that would be returned by executing
614 # table_column_names TBL
615 # The argument must be a table name. Return a list of column names, from
616 # left to right, for the table.
618 # table_column_decltypes TBL
619 # The argument must be a table name. Return a list of column declared
620 # types, from left to right, for the table.
622 proc sci {select cmd} {
624 set STMT [sqlite3_prepare_v2 db $select -1 dummy]
625 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
626 lappend res [$cmd $STMT $i]
628 sqlite3_finalize $STMT
631 proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
632 proc select_column_names {sql} { sci $sql sqlite3_column_name }
633 proc table_column_names {tbl} { tci $tbl sqlite3_column_name }
634 proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }
636 # Create a database schema. This schema is used by tests 2.1.* through 2.3.*.
639 do_execsql_test e_createtable-2.0 {
640 CREATE TABLE t1(a, b, c);
641 CREATE TABLE t2(d, e, f);
642 CREATE TABLE t3(g BIGINT, h VARCHAR(10));
643 CREATE TABLE t4(i BLOB, j ANYOLDATA);
644 CREATE TABLE t5(k FLOAT, l INTEGER);
645 CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));
646 CREATE TABLE t7(x INTEGER PRIMARY KEY);
647 CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');
648 CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);
651 # EVIDENCE-OF: R-64828-59568 The table has the same number of columns as
652 # the rows returned by the SELECT statement. The name of each column is
653 # the same as the name of the corresponding column in the result set of
654 # the SELECT statement.
656 do_createtable_tests 2.1 -tclquery {
657 table_column_names x1
659 catchsql { DROP TABLE x1 }
661 1 "CREATE TABLE x1 AS SELECT * FROM t1" {a b c}
662 2 "CREATE TABLE x1 AS SELECT c, b, a FROM t1" {c b a}
663 3 "CREATE TABLE x1 AS SELECT * FROM t1, t2" {a b c d e f}
664 4 "CREATE TABLE x1 AS SELECT count(*) FROM t1" {count(*)}
665 5 "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}
668 # EVIDENCE-OF: R-37111-22855 The declared type of each column is
669 # determined by the expression affinity of the corresponding expression
670 # in the result set of the SELECT statement, as follows: Expression
671 # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"
672 # REAL "REAL" NONE "" (empty string)
674 do_createtable_tests 2.2 -tclquery {
675 table_column_decltypes x1
677 catchsql { DROP TABLE x1 }
679 1 "CREATE TABLE x1 AS SELECT a FROM t1" {""}
680 2 "CREATE TABLE x1 AS SELECT * FROM t3" {INT TEXT}
681 3 "CREATE TABLE x1 AS SELECT * FROM t4" {"" NUM}
682 4 "CREATE TABLE x1 AS SELECT * FROM t5" {REAL INT}
685 # EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has
686 # no PRIMARY KEY and no constraints of any kind. The default value of
687 # each column is NULL. The default collation sequence for each column of
688 # the new table is BINARY.
690 # The following tests create tables based on SELECT statements that read
691 # from tables that have primary keys, constraints and explicit default
692 # collation sequences. None of this is transfered to the definition of
693 # the new table as stored in the sqlite_master table.
695 # Tests 2.3.2.* show that the default value of each column is NULL.
697 do_createtable_tests 2.3.1 -query {
698 SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1
700 1 "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}
701 2 "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}
702 3 "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}
703 4 "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}
705 do_execsql_test e_createtable-2.3.2.1 {
706 INSERT INTO x1 DEFAULT VALUES;
707 INSERT INTO x2 DEFAULT VALUES;
708 INSERT INTO x3 DEFAULT VALUES;
709 INSERT INTO x4 DEFAULT VALUES;
712 do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}
713 do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}
714 do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}
715 do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}
719 do_execsql_test e_createtable-2.4.0 {
720 CREATE TABLE t1(x, y);
721 INSERT INTO t1 VALUES('i', 'one');
722 INSERT INTO t1 VALUES('ii', 'two');
723 INSERT INTO t1 VALUES('iii', 'three');
726 # EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are
727 # initially populated with the rows of data returned by the SELECT
730 # EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending
731 # rowid values, starting with 1, in the order that they are returned by
732 # the SELECT statement.
734 # Each test case below is specified as the name of a table to create
735 # using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in
736 # creating it. The table is created.
738 # Test cases 2.4.*.1 check that after it has been created, the data in the
739 # table is the same as the data returned by the SELECT statement executed as
740 # a standalone command, verifying the first testable statement above.
742 # Test cases 2.4.*.2 check that the rowids were allocated contiguously
743 # as required by the second testable statement above. That the rowids
744 # from the contiguous block were allocated to rows in the order rows are
745 # returned by the SELECT statement is verified by 2.4.*.1.
747 # EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement
748 # creates and populates a database table based on the results of a
751 # The above is also considered to be tested by the following. It is
752 # clear that tables are being created and populated by the command in
755 foreach {tn tbl select} {
756 1 x1 "SELECT * FROM t1"
757 2 x2 "SELECT * FROM t1 ORDER BY x DESC"
758 3 x3 "SELECT * FROM t1 ORDER BY x ASC"
760 # Create the table using a "CREATE TABLE ... AS SELECT ..." command.
761 execsql [subst {CREATE TABLE $tbl AS $select}]
763 # Check that the rows inserted into the table, sorted in ascending rowid
764 # order, match those returned by executing the SELECT statement as a
765 # standalone command.
766 do_execsql_test e_createtable-2.4.$tn.1 [subst {
767 SELECT * FROM $tbl ORDER BY rowid;
770 # Check that the rowids in the new table are a contiguous block starting
771 # with rowid 1. Note that this will fail if SELECT statement $select
772 # returns 0 rows (as max(rowid) will be NULL).
773 do_execsql_test e_createtable-2.4.$tn.2 [subst {
774 SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl
778 #--------------------------------------------------------------------------
779 # Test cases for column defintions in CREATE TABLE statements that do not
780 # use a SELECT statement. Not including data constraints. In other words,
781 # tests for the specification of:
784 # * default values, and
785 # * default collation sequences.
788 # EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not
789 # restrict the type of data that may be inserted into a column based on
790 # the columns declared type.
792 # Test this by creating a few tables with varied declared types, then
793 # inserting various different types of values into them.
796 do_execsql_test e_createtable-3.1.0 {
797 CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);
798 CREATE TABLE t2(a DATETIME, b STRING, c REAL);
799 CREATE TABLE t3(o, t);
802 # value type -> declared column type
803 # ----------------------------------
804 # integer -> VARCHAR(10)
808 do_execsql_test e_createtable-3.1.1 {
809 INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');
811 } {14 {quite a lengthy string} UVU}
817 do_execsql_test e_createtable-3.1.2 {
818 INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');
820 } {{not a datetime} 13 12:41:59}
822 # EVIDENCE-OF: R-10565-09557 The declared type of a column is used to
823 # determine the affinity of the column only.
825 # Affinities are tested in more detail elsewhere (see document
826 # datatype3.html). Here, just test that affinity transformations
827 # consistent with the expected affinity of each column (based on
828 # the declared type) appear to take place.
830 # Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL
831 # Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL
832 # Affinities of t3 (test cases 3.2.3.*): NONE, NONE
834 do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {}
836 do_createtable_tests 3.2.1 -query {
837 SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1;
839 1 "INSERT INTO t1 VALUES(15, '22.0', '14')" {'15' 22 14.0}
840 2 "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)" {'22.0' 22 22.0}
842 do_createtable_tests 3.2.2 -query {
843 SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1;
845 1 "INSERT INTO t2 VALUES(15, '22.0', '14')" {15 22 14.0}
846 2 "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)" {22 22 22.0}
848 do_createtable_tests 3.2.3 -query {
849 SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1;
851 1 "INSERT INTO t3 VALUES('15', '22.0')" {'15' '22.0'}
852 2 "INSERT INTO t3 VALUES(15, 22.0)" {15 22.0}
855 # EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause
856 # attached to a column definition, then the default value of the column
859 # None of the columns in table t1 have an explicit DEFAULT clause.
860 # So testing that the default value of all columns in table t1 is
861 # NULL serves to verify the above.
863 do_createtable_tests 3.2.3 -query {
864 SELECT quote(x), quote(y), quote(z) FROM t1
866 execsql { DELETE FROM t1 }
868 1 "INSERT INTO t1(x, y) VALUES('abc', 'xyz')" {'abc' 'xyz' NULL}
869 2 "INSERT INTO t1(x, z) VALUES('abc', 'xyz')" {'abc' NULL 'xyz'}
870 3 "INSERT INTO t1 DEFAULT VALUES" {NULL NULL NULL}
873 # EVIDENCE-OF: R-62940-43005 An explicit DEFAULT clause may specify that
874 # the default value is NULL, a string constant, a blob constant, a
875 # signed-number, or any constant expression enclosed in parentheses. An
876 # explicit default value may also be one of the special case-independent
877 # keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
879 do_execsql_test e_createtable-3.3.1 {
882 b DEFAULT 'string constant',
883 c DEFAULT X'424C4F42',
888 h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),
889 i DEFAULT CURRENT_TIME,
890 j DEFAULT CURRENT_DATE,
891 k DEFAULT CURRENT_TIMESTAMP
895 # EVIDENCE-OF: R-10288-43169 For the purposes of the DEFAULT clause, an
896 # expression is considered constant provided that it does not contain
897 # any sub-queries or string constants enclosed in double quotes.
899 do_createtable_tests 3.4.1 -error {
900 default value of column [x] is not constant
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) ))} {}
907 do_createtable_tests 3.4.2 -repair {
908 catchsql { DROP TABLE t5 }
910 1 {CREATE TABLE t5(x DEFAULT ( 'abc' ))} {}
911 2 {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))} {}
914 # EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table
915 # by an INSERT statement that does not provide explicit values for all
916 # table columns the values stored in the new row are determined by their
919 # Verify this with some assert statements for which all, some and no
920 # columns lack explicit values.
922 set sqlite_current_time 1000000000
923 do_createtable_tests 3.5 -query {
924 SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f),
925 quote(g), quote(h), quote(i), quote(j), quote(k)
926 FROM t4 ORDER BY rowid DESC LIMIT 1;
928 1 "INSERT INTO t4 DEFAULT VALUES" {
929 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14
930 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
933 2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {
934 1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
937 3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {
938 NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1
941 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)" {
942 1 2 3 4 5 6 7 8 9 10 11
946 # EVIDENCE-OF: R-12572-62501 If the default value of the column is a
947 # constant NULL, text, blob or signed-number value, then that value is
948 # used directly in the new row.
950 do_execsql_test e_createtable-3.6.1 {
953 b DEFAULT 'text value',
954 c DEFAULT X'424C4F42',
959 do_execsql_test e_createtable-3.6.2 {
960 INSERT INTO t5 DEFAULT VALUES;
961 SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
962 } {NULL {'text value'} X'424C4F42' -45678.6 394507}
964 # EVIDENCE-OF: R-60616-50251 If the default value of a column is an
965 # expression in parentheses, then the expression is evaluated once for
966 # each row inserted and the results used in the new row.
968 # Test case 3.6.4 demonstrates that the expression is evaluated
969 # separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
973 proc nextint {} { incr ::nextint }
974 db func nextint nextint
976 do_execsql_test e_createtable-3.7.1 {
977 CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));
979 do_execsql_test e_createtable-3.7.2 {
980 INSERT INTO t6 DEFAULT VALUES;
981 SELECT quote(a), quote(b) FROM t6;
983 do_execsql_test e_createtable-3.7.3 {
984 INSERT INTO t6(a) VALUES('X');
985 SELECT quote(a), quote(b) FROM t6;
987 do_execsql_test e_createtable-3.7.4 {
988 INSERT INTO t6(a) SELECT a FROM t6;
989 SELECT quote(a), quote(b) FROM t6;
990 } {1 2 'X' 3 1 4 'X' 5}
992 # EVIDENCE-OF: R-18683-56219 If the default value of a column is
993 # CURRENT_TIME, CURRENT_DATE or CURRENT_DATETIME, then the value used in
994 # the new row is a text representation of the current UTC date and/or
997 # This is difficult to test literally without knowing what time the
998 # user will run the tests. Instead, we test that the three cases
999 # above set the value to the current date and/or time according to
1000 # the xCurrentTime() method of the VFS. Which is usually the same
1001 # as UTC. In this case, however, we instrument it to always return
1002 # a time equivalent to "2001-09-09 01:46:40 UTC".
1004 set sqlite_current_time 1000000000
1005 do_execsql_test e_createtable-3.8.1 {
1007 a DEFAULT CURRENT_TIME,
1008 b DEFAULT CURRENT_DATE,
1009 c DEFAULT CURRENT_TIMESTAMP
1012 do_execsql_test e_createtable-3.8.2 {
1013 INSERT INTO t7 DEFAULT VALUES;
1014 SELECT quote(a), quote(b), quote(c) FROM t7;
1015 } {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}}
1018 # EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value
1021 # EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".
1023 # EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is
1024 # "YYYY-MM-DD HH:MM:SS".
1026 # The three above are demonstrated by tests 1, 2 and 3 below.
1029 do_createtable_tests 3.8.3 -query {
1030 SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1;
1032 1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y}
1033 2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x}
1034 3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}}
1037 # EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a
1038 # collating sequence to use as the default collation sequence for the
1041 # EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the
1042 # default collation sequence is BINARY.
1044 do_execsql_test e_createtable-3-9.1 {
1045 CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);
1046 INSERT INTO t8 VALUES('abc', 'abc', 'abc', 'abc');
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');
1051 do_createtable_tests 3.9 {
1052 2 "SELECT a FROM t8 ORDER BY a, rowid" {abc ABC {abc } {ABC }}
1053 3 "SELECT b FROM t8 ORDER BY b, rowid" {{ABC } ABC abc {abc }}
1054 4 "SELECT c FROM t8 ORDER BY c, rowid" {ABC {ABC } abc {abc }}
1055 5 "SELECT d FROM t8 ORDER BY d, rowid" {ABC {ABC } abc {abc }}
1058 # EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited
1059 # by the SQLITE_MAX_COLUMN compile-time parameter.
1063 for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }
1066 do_execsql_test e_createtable-3.10.1 [subst {
1067 CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);
1069 do_catchsql_test e_createtable-3.10.2 [subst {
1070 CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);
1071 }] {1 {too many columns on t10}}
1073 # EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at
1074 # runtime using the sqlite3_limit() C/C++ interface.
1076 # A 30,000 byte blob consumes 30,003 bytes of record space. A record
1077 # that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests
1078 # 3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered
1079 # at runtime, are based on this calculation.
1081 sqlite3_limit db SQLITE_LIMIT_COLUMN 500
1082 do_execsql_test e_createtable-3.11.1 [subst {
1083 CREATE TABLE t10([columns 500]);
1085 do_catchsql_test e_createtable-3.11.2 [subst {
1086 CREATE TABLE t11([columns 501]);
1087 }] {1 {too many columns on t11}}
1089 # Check that it is not possible to raise the column limit above its
1090 # default compile time value.
1092 sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]
1093 do_catchsql_test e_createtable-3.11.3 [subst {
1094 CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);
1095 }] {1 {too many columns on t11}}
1097 sqlite3_limit db SQLITE_LIMIT_LENGTH 90010
1098 do_execsql_test e_createtable-3.11.4 {
1099 CREATE TABLE t12(a, b, c);
1100 INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
1102 do_catchsql_test e_createtable-3.11.5 {
1103 INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
1104 } {1 {string or blob too big}}
1106 #-------------------------------------------------------------------------
1107 # Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT
1108 # NULL and CHECK constraints).
1111 # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one
1114 # EVIDENCE-OF: R-18080-47271 If there is more than one PRIMARY KEY
1115 # clause in a single CREATE TABLE statement, it is an error.
1117 # To test the two above, show that zero primary keys is Ok, one primary
1118 # key is Ok, and two or more primary keys is an error.
1121 do_createtable_tests 4.1.1 {
1122 1 "CREATE TABLE t1(a, b, c)" {}
1123 2 "CREATE TABLE t2(a PRIMARY KEY, b, c)" {}
1124 3 "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))" {}
1125 4 "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))" {}
1127 do_createtable_tests 4.1.2 -error {
1128 table "t5" has more than one primary key
1130 1 "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)" {}
1131 2 "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))" {}
1132 3 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)" {}
1133 4 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}
1134 5 "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))" {}
1135 6 "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))" {}
1138 proc table_pk {tbl} {
1140 db eval "pragma table_info($tbl)" a {
1141 if {$a(pk)} { lappend pk $a(name) }
1146 # EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a
1147 # column definition, then the primary key for the table consists of that
1150 # The above is tested by 4.2.1.*
1152 # EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as
1153 # a table-constraint, then the primary key of the table consists of the
1154 # list of columns specified as part of the PRIMARY KEY clause.
1156 # The above is tested by 4.2.2.*
1158 do_createtable_tests 4.2 -repair {
1159 catchsql { DROP TABLE t5 }
1163 1.1 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}
1164 1.2 "CREATE TABLE t5(a PRIMARY KEY, b, c)" {a}
1166 2.1 "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))" {a}
1167 2.2 "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))" {a b c}
1168 2.3 "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)" {b}
1171 # EVIDENCE-OF: R-33986-09410 Each row in a table with a primary key must
1172 # feature a unique combination of values in its primary key columns.
1174 # EVIDENCE-OF: R-39102-06737 If an INSERT or UPDATE statement attempts
1175 # to modify the table content so that two or more rows feature identical
1176 # primary key values, it is a constraint violation.
1179 do_execsql_test 4.3.0 {
1180 CREATE TABLE t1(x PRIMARY KEY, y);
1181 INSERT INTO t1 VALUES(0, 'zero');
1182 INSERT INTO t1 VALUES(45.5, 'one');
1183 INSERT INTO t1 VALUES('brambles', 'two');
1184 INSERT INTO t1 VALUES(X'ABCDEF', 'three');
1186 CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
1187 INSERT INTO t2 VALUES(0, 'zero');
1188 INSERT INTO t2 VALUES(45.5, 'one');
1189 INSERT INTO t2 VALUES('brambles', 'two');
1190 INSERT INTO t2 VALUES(X'ABCDEF', 'three');
1193 do_createtable_tests 4.3.1 -error { %s not unique } {
1194 1 "INSERT INTO t1 VALUES(0, 0)" {"column x is"}
1195 2 "INSERT INTO t1 VALUES(45.5, 'abc')" {"column x is"}
1196 3 "INSERT INTO t1 VALUES(0.0, 'abc')" {"column x is"}
1197 4 "INSERT INTO t1 VALUES('brambles', 'abc')" {"column x is"}
1198 5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {"column x is"}
1200 6 "INSERT INTO t2 VALUES(0, 'zero')" {"columns x, y are"}
1201 7 "INSERT INTO t2 VALUES(45.5, 'one')" {"columns x, y are"}
1202 8 "INSERT INTO t2 VALUES(0.0, 'zero')" {"columns x, y are"}
1203 9 "INSERT INTO t2 VALUES('brambles', 'two')" {"columns x, y are"}
1204 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {"columns x, y are"}
1206 do_createtable_tests 4.3.2 {
1207 1 "INSERT INTO t1 VALUES(-1, 0)" {}
1208 2 "INSERT INTO t1 VALUES(45.2, 'abc')" {}
1209 3 "INSERT INTO t1 VALUES(0.01, 'abc')" {}
1210 4 "INSERT INTO t1 VALUES('bramble', 'abc')" {}
1211 5 "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')" {}
1213 6 "INSERT INTO t2 VALUES(0, 0)" {}
1214 7 "INSERT INTO t2 VALUES(45.5, 'abc')" {}
1215 8 "INSERT INTO t2 VALUES(0.0, 'abc')" {}
1216 9 "INSERT INTO t2 VALUES('brambles', 'abc')" {}
1217 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')" {}
1219 do_createtable_tests 4.3.3 -error { %s not unique } {
1220 1 "UPDATE t1 SET x=0 WHERE y='two'" {"column x is"}
1221 2 "UPDATE t1 SET x='brambles' WHERE y='three'" {"column x is"}
1222 3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {"column x is"}
1223 4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {"column x is"}
1224 5 "UPDATE t1 SET x=0.0 WHERE y='three'" {"column x is"}
1226 6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {"columns x, y are"}
1227 7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"
1228 {"columns x, y are"}
1229 8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
1230 9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'"
1231 {"columns x, y are"}
1232 10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'"
1233 {"columns x, y are"}
1237 # EVIDENCE-OF: R-52572-02078 For the purposes of determining the
1238 # uniqueness of primary key values, NULL values are considered distinct
1239 # from all other values, including other NULLs.
1241 do_createtable_tests 4.4 {
1242 1 "INSERT INTO t1 VALUES(NULL, 0)" {}
1243 2 "INSERT INTO t1 VALUES(NULL, 0)" {}
1244 3 "INSERT INTO t1 VALUES(NULL, 0)" {}
1246 4 "INSERT INTO t2 VALUES(NULL, 'zero')" {}
1247 5 "INSERT INTO t2 VALUES(NULL, 'one')" {}
1248 6 "INSERT INTO t2 VALUES(NULL, 'two')" {}
1249 7 "INSERT INTO t2 VALUES(NULL, 'three')" {}
1251 8 "INSERT INTO t2 VALUES(0, NULL)" {}
1252 9 "INSERT INTO t2 VALUES(45.5, NULL)" {}
1253 10 "INSERT INTO t2 VALUES(0.0, NULL)" {}
1254 11 "INSERT INTO t2 VALUES('brambles', NULL)" {}
1255 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {}
1257 13 "INSERT INTO t2 VALUES(NULL, NULL)" {}
1258 14 "INSERT INTO t2 VALUES(NULL, NULL)" {}
1261 # EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY
1262 # SQLite allows NULL values in a PRIMARY KEY column.
1264 # If the column is an integer primary key, attempting to insert a NULL
1265 # into the column triggers the auto-increment behaviour. Attempting
1266 # to use UPDATE to set an ipk column to a NULL value is an error.
1268 do_createtable_tests 4.5.1 {
1269 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3
1270 2 "SELECT count(*) FROM t2 WHERE x IS NULL" 6
1271 3 "SELECT count(*) FROM t2 WHERE y IS NULL" 7
1272 4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 2
1274 do_execsql_test 4.5.2 {
1275 CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
1276 INSERT INTO t3 VALUES(1, NULL, 2);
1277 INSERT INTO t3 VALUES('x', NULL, 'y');
1280 do_catchsql_test 4.5.3 {
1281 INSERT INTO t3 VALUES(2, 5, 3);
1282 UPDATE t3 SET u = NULL WHERE s = 2;
1283 } {1 {datatype mismatch}}
1285 # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
1286 # KEY constraint, except that a single table may have any number of
1287 # UNIQUE constraints.
1290 do_createtable_tests 4.6 {
1291 1 "CREATE TABLE t1(a UNIQUE, b UNIQUE)" {}
1292 2 "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))" {}
1293 3 "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {}
1294 4 "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))" {}
1297 # EVIDENCE-OF: R-55240-58877 For each UNIQUE constraint on the table,
1298 # each row must feature a unique combination of values in the columns
1299 # identified by the UNIQUE constraint.
1301 # EVIDENCE-OF: R-47733-51480 If an INSERT or UPDATE statement attempts
1302 # to modify the table content so that two or more rows feature identical
1303 # values in a set of columns that are subject to a UNIQUE constraint, it
1304 # is a constraint violation.
1306 do_execsql_test 4.7.0 {
1307 INSERT INTO t1 VALUES(1, 2);
1308 INSERT INTO t1 VALUES(4.3, 5.5);
1309 INSERT INTO t1 VALUES('reveal', 'variableness');
1310 INSERT INTO t1 VALUES(X'123456', X'654321');
1312 INSERT INTO t4 VALUES('xyx', 1, 1);
1313 INSERT INTO t4 VALUES('xyx', 2, 1);
1314 INSERT INTO t4 VALUES('uvw', 1, 1);
1316 do_createtable_tests 4.7.1 -error { %s not unique } {
1317 1 "INSERT INTO t1 VALUES(1, 'one')" {{column a is}}
1318 2 "INSERT INTO t1 VALUES(4.3, 'two')" {{column a is}}
1319 3 "INSERT INTO t1 VALUES('reveal', 'three')" {{column a is}}
1320 4 "INSERT INTO t1 VALUES(X'123456', 'four')" {{column a is}}
1322 5 "UPDATE t1 SET a = 1 WHERE rowid=2" {{column a is}}
1323 6 "UPDATE t1 SET a = 4.3 WHERE rowid=3" {{column a is}}
1324 7 "UPDATE t1 SET a = 'reveal' WHERE rowid=4" {{column a is}}
1325 8 "UPDATE t1 SET a = X'123456' WHERE rowid=1" {{column a is}}
1327 9 "INSERT INTO t4 VALUES('xyx', 1, 1)" {{columns a, b, c are}}
1328 10 "INSERT INTO t4 VALUES('xyx', 2, 1)" {{columns a, b, c are}}
1329 11 "INSERT INTO t4 VALUES('uvw', 1, 1)" {{columns a, b, c are}}
1331 12 "UPDATE t4 SET a='xyx' WHERE rowid=3" {{columns a, b, c are}}
1332 13 "UPDATE t4 SET b=1 WHERE rowid=2" {{columns a, b, c are}}
1333 14 "UPDATE t4 SET a=0, b=0, c=0" {{columns a, b, c are}}
1336 # EVIDENCE-OF: R-21289-11559 As with PRIMARY KEY constraints, for the
1337 # purposes of UNIQUE constraints NULL values are considered distinct
1338 # from all other values (including other NULLs).
1340 do_createtable_tests 4.8 {
1341 1 "INSERT INTO t1 VALUES(NULL, NULL)" {}
1342 2 "INSERT INTO t1 VALUES(NULL, NULL)" {}
1343 3 "UPDATE t1 SET a = NULL" {}
1344 4 "UPDATE t1 SET b = NULL" {}
1346 5 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {}
1347 6 "INSERT INTO t4 VALUES(NULL, NULL, NULL)" {}
1348 7 "UPDATE t4 SET a = NULL" {}
1349 8 "UPDATE t4 SET b = NULL" {}
1350 9 "UPDATE t4 SET c = NULL" {}
1353 # EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both
1354 # UNIQUE and PRIMARY KEY constraints are implemented by creating an
1355 # index in the database (in the same way as a "CREATE UNIQUE INDEX"
1357 do_createtable_tests 4.9 -repair drop_all_tables -query {
1358 SELECT count(*) FROM sqlite_master WHERE type='index'
1360 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1
1361 2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0
1362 3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1
1363 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2
1364 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2
1367 # EVIDENCE-OF: R-02252-33116 Such an index is used like any other index
1368 # in the database to optimize queries.
1370 do_execsql_test 4.10.0 {
1371 CREATE TABLE t1(a, b PRIMARY KEY);
1372 CREATE TABLE t2(a, b, c, UNIQUE(b, c));
1374 do_createtable_tests 4.10 {
1375 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5"
1376 {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}
1378 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
1379 {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}
1381 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
1382 {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}}
1385 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
1386 # column definition or specified as a table constraint. In practice it
1387 # makes no difference.
1389 # All the tests that deal with CHECK constraints below (4.11.* and
1390 # 4.12.*) are run once for a table with the check constraint attached
1391 # to a column definition, and once with a table where the check
1392 # condition is specified as a table constraint.
1394 # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the
1395 # table or an existing row is updated, the expression associated with
1396 # each CHECK constraint is evaluated and cast to a NUMERIC value in the
1397 # same way as a CAST expression. If the result is zero (integer value 0
1398 # or real value 0.0), then a constraint violation has occurred.
1401 do_execsql_test 4.11 {
1402 CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));
1403 CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));
1404 INSERT INTO x1 VALUES('x', 'xx');
1405 INSERT INTO x1 VALUES('y', 'yy');
1406 INSERT INTO t1 SELECT * FROM x1;
1408 CREATE TABLE x2(a CHECK( a||b ), b);
1409 CREATE TABLE t2(a, b, CHECK( a||b ));
1410 INSERT INTO x2 VALUES(1, 'xx');
1411 INSERT INTO x2 VALUES(1, 'yy');
1412 INSERT INTO t2 SELECT * FROM x2;
1415 do_createtable_tests 4.11 -error {constraint failed} {
1416 1a "INSERT INTO x1 VALUES('one', 0)" {}
1417 1b "INSERT INTO t1 VALUES('one', -4.0)" {}
1419 2a "INSERT INTO x2 VALUES('abc', 1)" {}
1420 2b "INSERT INTO t2 VALUES('abc', 1)" {}
1422 3a "INSERT INTO x2 VALUES(0, 'abc')" {}
1423 3b "INSERT INTO t2 VALUES(0, 'abc')" {}
1425 4a "UPDATE t1 SET b=-1 WHERE rowid=1" {}
1426 4b "UPDATE x1 SET b=-1 WHERE rowid=1" {}
1428 4a "UPDATE x2 SET a='' WHERE rowid=1" {}
1429 4b "UPDATE t2 SET a='' WHERE rowid=1" {}
1432 # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,
1433 # or any other non-zero value, it is not a constraint violation.
1435 do_createtable_tests 4.12 {
1436 1a "INSERT INTO x1 VALUES('one', NULL)" {}
1437 1b "INSERT INTO t1 VALUES('one', NULL)" {}
1439 2a "INSERT INTO x1 VALUES('one', 2)" {}
1440 2b "INSERT INTO t1 VALUES('one', 2)" {}
1442 3a "INSERT INTO x2 VALUES(1, 'abc')" {}
1443 3b "INSERT INTO t2 VALUES(1, 'abc')" {}
1446 # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached
1447 # to a column definition, not specified as a table constraint.
1450 do_createtable_tests 4.13.1 {
1451 1 "CREATE TABLE t1(a NOT NULL, b)" {}
1452 2 "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)" {}
1453 3 "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)" {}
1455 do_createtable_tests 4.13.2 -error {
1456 near "NOT": syntax error
1458 1 "CREATE TABLE t4(a, b, NOT NULL(a))" {}
1459 2 "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))" {}
1460 3 "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))" {}
1463 # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the
1464 # associated column may not contain a NULL value. Attempting to set the
1465 # column value to NULL when inserting a new row or updating an existing
1466 # one causes a constraint violation.
1468 # These tests use the tables created by 4.13.
1470 do_execsql_test 4.14.0 {
1471 INSERT INTO t1 VALUES('x', 'y');
1472 INSERT INTO t1 VALUES('z', NULL);
1474 INSERT INTO t2 VALUES('x', 'y');
1475 INSERT INTO t2 VALUES('z', NULL);
1477 INSERT INTO t3 VALUES('x', 'y', 'z');
1478 INSERT INTO t3 VALUES(1, 2, 3);
1480 do_createtable_tests 4.14 -error {
1483 1 "INSERT INTO t1 VALUES(NULL, 'a')" {t1.a}
1484 2 "INSERT INTO t2 VALUES(NULL, 'b')" {t2.a}
1485 3 "INSERT INTO t3 VALUES('c', 'd', NULL)" {t3.c}
1486 4 "INSERT INTO t3 VALUES('e', NULL, 'f')" {t3.b}
1487 5 "INSERT INTO t3 VALUES(NULL, 'g', 'h')" {t3.a}
1490 # EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL
1491 # constraints may be explicitly assigned a default conflict resolution
1492 # algorithm by including a conflict-clause in their definitions.
1494 # Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE
1496 # Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL
1497 # and UNIQUE constraints, respectively.
1500 do_execsql_test 4.15.0 {
1501 CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b);
1502 CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b);
1503 CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b);
1504 CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b);
1505 CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b);
1506 CREATE TABLE t1_xx(a PRIMARY KEY, b);
1508 INSERT INTO t1_ab VALUES(1, 'one');
1509 INSERT INTO t1_ab VALUES(2, 'two');
1510 INSERT INTO t1_ro SELECT * FROM t1_ab;
1511 INSERT INTO t1_ig SELECT * FROM t1_ab;
1512 INSERT INTO t1_fa SELECT * FROM t1_ab;
1513 INSERT INTO t1_re SELECT * FROM t1_ab;
1514 INSERT INTO t1_xx SELECT * FROM t1_ab;
1516 CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT);
1517 CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK);
1518 CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE);
1519 CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL);
1520 CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE);
1521 CREATE TABLE t2_xx(a, b NOT NULL);
1523 INSERT INTO t2_ab VALUES(1, 'one');
1524 INSERT INTO t2_ab VALUES(2, 'two');
1525 INSERT INTO t2_ro SELECT * FROM t2_ab;
1526 INSERT INTO t2_ig SELECT * FROM t2_ab;
1527 INSERT INTO t2_fa SELECT * FROM t2_ab;
1528 INSERT INTO t2_re SELECT * FROM t2_ab;
1529 INSERT INTO t2_xx SELECT * FROM t2_ab;
1531 CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT);
1532 CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK);
1533 CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE);
1534 CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL);
1535 CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE);
1536 CREATE TABLE t3_xx(a, b, UNIQUE(a, b));
1538 INSERT INTO t3_ab VALUES(1, 'one');
1539 INSERT INTO t3_ab VALUES(2, 'two');
1540 INSERT INTO t3_ro SELECT * FROM t3_ab;
1541 INSERT INTO t3_ig SELECT * FROM t3_ab;
1542 INSERT INTO t3_fa SELECT * FROM t3_ab;
1543 INSERT INTO t3_re SELECT * FROM t3_ab;
1544 INSERT INTO t3_xx SELECT * FROM t3_ab;
1547 foreach {tn tbl res ac data} {
1548 1 t1_ab {1 {column a is not unique}} 0 {1 one 2 two 3 three}
1549 2 t1_ro {1 {column a is not unique}} 1 {1 one 2 two}
1550 3 t1_fa {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string}
1551 4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
1552 5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
1553 6 t1_xx {1 {column a is not unique}} 0 {1 one 2 two 3 three}
1556 do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1558 do_catchsql_test 4.15.$tn.2 "
1559 INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
1562 do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac
1563 do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
1565 foreach {tn tbl res ac data} {
1566 1 t2_ab {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three}
1567 2 t2_ro {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two}
1568 3 t2_fa {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx}
1569 4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
1570 5 t2_re {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three}
1571 6 t2_xx {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three}
1574 do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1576 do_catchsql_test 4.16.$tn.2 "
1577 INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
1580 do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac
1581 do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
1583 foreach {tn tbl res ac data} {
1584 1 t3_ab {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
1585 2 t3_ro {1 {columns a, b are not unique}} 1 {1 one 2 two}
1586 3 t3_fa {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three}
1587 4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
1588 5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
1589 6 t3_xx {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
1592 do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1594 do_catchsql_test 4.17.$tn.2 "
1595 INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
1598 do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
1599 do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data
1603 # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
1604 # include a conflict-clause or it is a CHECK constraint, the default
1605 # conflict resolution algorithm is ABORT.
1607 # The first half of the above is tested along with explicit ON
1608 # CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx
1609 # and t3_xx). The following just tests that the default conflict
1610 # handling for CHECK constraints is ABORT.
1612 do_execsql_test 4.18.1 {
1613 CREATE TABLE t4(a, b CHECK (b!=10));
1614 INSERT INTO t4 VALUES(1, 2);
1615 INSERT INTO t4 VALUES(3, 4);
1617 do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
1618 do_catchsql_test 4.18.3 {
1619 INSERT INTO t4 SELECT a+4, b+4 FROM t4
1620 } {1 {constraint failed}}
1621 do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0
1622 do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}
1624 # EVIDENCE-OF: R-19114-56113 Different constraints within the same table
1625 # may have different default conflict resolution algorithms.
1627 do_execsql_test 4.19.0 {
1628 CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
1630 do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
1631 do_execsql_test 4.19.2 { SELECT * FROM t5 } {}
1632 do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
1633 {1 {t5.b may not be NULL}}
1634 do_execsql_test 4.19.4 { SELECT * FROM t5 } {}
1636 #------------------------------------------------------------------------
1637 # Tests for INTEGER PRIMARY KEY and rowid related statements.
1640 # EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
1641 # of the special case-independent names "rowid", "oid", or "_rowid_" in
1642 # place of a column name.
1645 do_execsql_test 5.1.0 {
1646 CREATE TABLE t1(x, y);
1647 INSERT INTO t1 VALUES('one', 'first');
1648 INSERT INTO t1 VALUES('two', 'second');
1649 INSERT INTO t1 VALUES('three', 'third');
1651 do_createtable_tests 5.1 {
1652 1 "SELECT rowid FROM t1" {1 2 3}
1653 2 "SELECT oid FROM t1" {1 2 3}
1654 3 "SELECT _rowid_ FROM t1" {1 2 3}
1655 4 "SELECT ROWID FROM t1" {1 2 3}
1656 5 "SELECT OID FROM t1" {1 2 3}
1657 6 "SELECT _ROWID_ FROM t1" {1 2 3}
1658 7 "SELECT RoWiD FROM t1" {1 2 3}
1659 8 "SELECT OiD FROM t1" {1 2 3}
1660 9 "SELECT _RoWiD_ FROM t1" {1 2 3}
1663 # EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
1664 # named "rowid", "oid" or "_rowid_", then that name always refers the
1665 # explicitly declared column and cannot be used to retrieve the integer
1668 do_execsql_test 5.2.0 {
1669 CREATE TABLE t2(oid, b);
1670 CREATE TABLE t3(a, _rowid_);
1671 CREATE TABLE t4(a, b, rowid);
1673 INSERT INTO t2 VALUES('one', 'two');
1674 INSERT INTO t2 VALUES('three', 'four');
1676 INSERT INTO t3 VALUES('five', 'six');
1677 INSERT INTO t3 VALUES('seven', 'eight');
1679 INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
1680 INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
1682 do_createtable_tests 5.2 {
1683 1 "SELECT oid, rowid, _rowid_ FROM t2" {one 1 1 three 2 2}
1684 2 "SELECT oid, rowid, _rowid_ FROM t3" {1 1 six 2 2 eight}
1685 3 "SELECT oid, rowid, _rowid_ FROM t4" {1 eleven 1 2 fourteen 2}
1689 # Argument $tbl is the name of a table in the database. Argument $col is
1690 # the name of one of the tables columns. Return 1 if $col is an alias for
1691 # the rowid, or 0 otherwise.
1693 proc is_integer_primary_key {tbl col} {
1694 lindex [db eval [subst {
1696 INSERT INTO $tbl ($col) VALUES(0);
1697 SELECT (rowid==$col) FROM $tbl;
1702 # EVIDENCE-OF: R-53738-31673 With one exception, if a table has a
1703 # primary key that consists of a single column, and the declared type of
1704 # that column is "INTEGER" in any mixture of upper and lower case, then
1705 # the column becomes an alias for the rowid.
1707 # EVIDENCE-OF: R-45951-08347 if the declaration of a column with
1708 # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does
1709 # not become an alias for the rowid and is not classified as an integer
1712 do_createtable_tests 5.3 -tclquery {
1713 is_integer_primary_key t5 pk
1715 catchsql { DROP TABLE t5 }
1717 1 "CREATE TABLE t5(pk integer primary key)" 1
1718 2 "CREATE TABLE t5(pk integer, primary key(pk))" 1
1719 3 "CREATE TABLE t5(pk integer, v integer, primary key(pk))" 1
1720 4 "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))" 0
1721 5 "CREATE TABLE t5(pk int, v integer, primary key(pk, v))" 0
1722 6 "CREATE TABLE t5(pk int, v integer, primary key(pk))" 0
1723 7 "CREATE TABLE t5(pk int primary key, v integer)" 0
1724 8 "CREATE TABLE t5(pk inTEger primary key)" 1
1725 9 "CREATE TABLE t5(pk inteGEr, primary key(pk))" 1
1726 10 "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))" 1
1729 # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or
1730 # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
1731 # key column to behave as an ordinary table column with integer affinity
1732 # and a unique index, not as an alias for the rowid.
1734 do_execsql_test 5.4.1 {
1735 CREATE TABLE t6(pk INT primary key);
1736 CREATE TABLE t7(pk BIGINT primary key);
1737 CREATE TABLE t8(pk SHORT INTEGER primary key);
1738 CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
1740 do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0
1741 do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0
1742 do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0
1743 do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0
1745 do_execsql_test 5.4.3 {
1746 INSERT INTO t6 VALUES('2.0');
1747 INSERT INTO t7 VALUES('2.0');
1748 INSERT INTO t8 VALUES('2.0');
1749 INSERT INTO t9 VALUES('2.0');
1750 SELECT typeof(pk), pk FROM t6;
1751 SELECT typeof(pk), pk FROM t7;
1752 SELECT typeof(pk), pk FROM t8;
1753 SELECT typeof(pk), pk FROM t9;
1754 } {integer 2 integer 2 integer 2 integer 2}
1756 do_catchsql_test 5.4.4.1 {
1757 INSERT INTO t6 VALUES(2)
1758 } {1 {column pk is not unique}}
1759 do_catchsql_test 5.4.4.2 {
1760 INSERT INTO t7 VALUES(2)
1761 } {1 {column pk is not unique}}
1762 do_catchsql_test 5.4.4.3 {
1763 INSERT INTO t8 VALUES(2)
1764 } {1 {column pk is not unique}}
1765 do_catchsql_test 5.4.4.4 {
1766 INSERT INTO t9 VALUES(2)
1767 } {1 {column pk is not unique}}
1769 # EVIDENCE-OF: R-56094-57830 the following three table declarations all
1770 # cause the column "x" to be an alias for the rowid (an integer primary
1771 # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
1772 # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
1773 # z, PRIMARY KEY(x DESC));
1775 # EVIDENCE-OF: R-20149-25884 the following declaration does not result
1776 # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY
1779 do_createtable_tests 5 -tclquery {
1780 is_integer_primary_key t x
1782 catchsql { DROP TABLE t }
1784 5.1 "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" 1
1785 5.2 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))" 1
1786 5.3 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1
1787 6.1 "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)" 0
1790 # EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
1791 # UPDATE statement in the same way as any other column value can, either
1792 # using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
1793 # using an alias created by an integer primary key.
1795 do_execsql_test 5.7.0 {
1796 CREATE TABLE t10(a, b);
1797 INSERT INTO t10 VALUES('ten', 10);
1799 CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
1800 INSERT INTO t11 VALUES('ten', 10);
1802 do_createtable_tests 5.7.1 -query {
1803 SELECT rowid, _rowid_, oid FROM t10;
1805 1 "UPDATE t10 SET rowid = 5" {5 5 5}
1806 2 "UPDATE t10 SET _rowid_ = 6" {6 6 6}
1807 3 "UPDATE t10 SET oid = 7" {7 7 7}
1809 do_createtable_tests 5.7.2 -query {
1810 SELECT rowid, _rowid_, oid, b FROM t11;
1812 1 "UPDATE t11 SET rowid = 5" {5 5 5 5}
1813 2 "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}
1814 3 "UPDATE t11 SET oid = 7" {7 7 7 7}
1815 4 "UPDATE t11 SET b = 8" {8 8 8 8}
1818 # EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide
1819 # a value to use as the rowid for each row inserted.
1821 do_createtable_tests 5.8.1 -query {
1822 SELECT rowid, _rowid_, oid FROM t10;
1824 execsql { DELETE FROM t10 }
1826 1 "INSERT INTO t10(oid) VALUES(15)" {15 15 15}
1827 2 "INSERT INTO t10(rowid) VALUES(16)" {16 16 16}
1828 3 "INSERT INTO t10(_rowid_) VALUES(17)" {17 17 17}
1829 4 "INSERT INTO t10(a, b, oid) VALUES(1,2,3)" {3 3 3}
1831 do_createtable_tests 5.8.2 -query {
1832 SELECT rowid, _rowid_, oid, b FROM t11;
1834 execsql { DELETE FROM t11 }
1836 1 "INSERT INTO t11(oid) VALUES(15)" {15 15 15 15}
1837 2 "INSERT INTO t11(rowid) VALUES(16)" {16 16 16 16}
1838 3 "INSERT INTO t11(_rowid_) VALUES(17)" {17 17 17 17}
1839 4 "INSERT INTO t11(a, b) VALUES(1,2)" {2 2 2 2}
1842 # EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer
1843 # primary key or rowid column must contain integer values. Integer
1844 # primary key or rowid columns are not able to hold floating point
1845 # values, strings, BLOBs, or NULLs.
1847 # This is considered by the tests for the following 3 statements,
1850 # 1. Attempts to UPDATE a rowid column to a non-integer value fail,
1851 # 2. Attempts to INSERT a real, string or blob value into a rowid
1853 # 3. Attempting to INSERT a NULL value into a rowid column causes the
1854 # system to automatically select an integer value to use.
1858 # EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an
1859 # integer primary key or rowid column to a NULL or blob value, or to a
1860 # string or real value that cannot be losslessly converted to an
1861 # integer, a "datatype mismatch" error occurs and the statement is
1865 do_execsql_test 5.9.0 {
1866 CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
1867 INSERT INTO t12 VALUES(5, 'five');
1869 do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {
1870 1 "UPDATE t12 SET x = 4" {integer 4}
1871 2 "UPDATE t12 SET x = 10.0" {integer 10}
1872 3 "UPDATE t12 SET x = '12.0'" {integer 12}
1873 4 "UPDATE t12 SET x = '-15.0'" {integer -15}
1875 do_createtable_tests 5.9.2 -error {
1878 1 "UPDATE t12 SET x = 4.1" {}
1879 2 "UPDATE t12 SET x = 'hello'" {}
1880 3 "UPDATE t12 SET x = NULL" {}
1881 4 "UPDATE t12 SET x = X'ABCD'" {}
1882 5 "UPDATE t12 SET x = X'3900'" {}
1883 6 "UPDATE t12 SET x = X'39'" {}
1886 # EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a
1887 # blob value, or a string or real value that cannot be losslessly
1888 # converted to an integer into an integer primary key or rowid column, a
1889 # "datatype mismatch" error occurs and the statement is aborted.
1891 do_execsql_test 5.10.0 { DELETE FROM t12 }
1892 do_createtable_tests 5.10.1 -error {
1895 1 "INSERT INTO t12(x) VALUES(4.1)" {}
1896 2 "INSERT INTO t12(x) VALUES('hello')" {}
1897 3 "INSERT INTO t12(x) VALUES(X'ABCD')" {}
1898 4 "INSERT INTO t12(x) VALUES(X'3900')" {}
1899 5 "INSERT INTO t12(x) VALUES(X'39')" {}
1901 do_createtable_tests 5.10.2 -query {
1902 SELECT typeof(x), x FROM t12
1904 execsql { DELETE FROM t12 }
1906 1 "INSERT INTO t12(x) VALUES(4)" {integer 4}
1907 2 "INSERT INTO t12(x) VALUES(10.0)" {integer 10}
1908 3 "INSERT INTO t12(x) VALUES('12.0')" {integer 12}
1909 4 "INSERT INTO t12(x) VALUES('4e3')" {integer 4000}
1910 5 "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}
1913 # EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a
1914 # NULL value into a rowid or integer primary key column, the system
1915 # chooses an integer value to use as the rowid automatically.
1917 do_execsql_test 5.11.0 { DELETE FROM t12 }
1918 do_createtable_tests 5.11 -query {
1919 SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)
1921 1 "INSERT INTO t12 DEFAULT VALUES" {integer 1}
1922 2 "INSERT INTO t12(y) VALUES(5)" {integer 2}
1923 3 "INSERT INTO t12(x,y) VALUES(NULL, 10)" {integer 3}
1924 4 "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12"
1925 {integer 4 integer 5 integer 6}
1926 5 "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"
1927 {integer 7 integer 8 integer 9}