SQLite

Check-in [fb8db5581d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests for CHECK and UNIQUE constraints to e_createtable.test.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fb8db5581d884e5eb380480bc08d2106bfb6a9fc
User & Date: dan 2010-10-01 19:04:37.000
Context
2010-10-04
10:45
Add tests for table-constraints to e_createtable.test. (check-in: 70f511872e user: dan tags: trunk)
2010-10-01
19:04
Add tests for CHECK and UNIQUE constraints to e_createtable.test. (check-in: fb8db5581d user: dan tags: trunk)
17:23
Add #ifndef USE_SYSTEM_SQLITE...#endif around the bundled SQLite library for the TCL bindings. (check-in: 8d85584a4e user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/e_createtable.test.
1278
1279
1280
1281
1282
1283
1284











1285



1286


















































































































































  SELECT u FROM t3;
} {1 2}
do_catchsql_test 4.5.3 {
  INSERT INTO t3 VALUES(2, 5, 3);
  UPDATE t3 SET u = NULL WHERE s = 2;
} {1 {datatype mismatch}}












finish_test





























































































































































>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
  SELECT u FROM t3;
} {1 2}
do_catchsql_test 4.5.3 {
  INSERT INTO t3 VALUES(2, 5, 3);
  UPDATE t3 SET u = NULL WHERE s = 2;
} {1 {datatype mismatch}}

# EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
# KEY constraint, except that a single table may have any number of
# UNIQUE constraints.
#
drop_all_tables
do_createtable_tests 4.6 {
  1    "CREATE TABLE t1(a UNIQUE, b UNIQUE)"                       {}
  2    "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))"             {}
  3    "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {}
  4    "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))"                 {}
}

# EVIDENCE-OF: R-55240-58877 For each UNIQUE constraint on the table,
# each row must feature a unique combination of values in the columns
# identified by the UNIQUE constraint.
#
# EVIDENCE-OF: R-47733-51480 If an INSERT or UPDATE statement attempts
# to modify the table content so that two or more rows feature identical
# values in a set of columns that are subject to a UNIQUE constraint, it
# is a constraint violation.
#
do_execsql_test 4.7.0 {
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(4.3, 5.5);
  INSERT INTO t1 VALUES('reveal', 'variableness');
  INSERT INTO t1 VALUES(X'123456', X'654321');

  INSERT INTO t4 VALUES('xyx', 1, 1);
  INSERT INTO t4 VALUES('xyx', 2, 1);
  INSERT INTO t4 VALUES('uvw', 1, 1);
}
do_createtable_tests 4.7.1 -error { %s not unique } {
  1    "INSERT INTO t1 VALUES(1, 'one')"             {{column a is}}
  2    "INSERT INTO t1 VALUES(4.3, 'two')"           {{column a is}}
  3    "INSERT INTO t1 VALUES('reveal', 'three')"    {{column a is}}
  4    "INSERT INTO t1 VALUES(X'123456', 'four')"    {{column a is}}

  5    "UPDATE t1 SET a = 1 WHERE rowid=2"           {{column a is}}
  6    "UPDATE t1 SET a = 4.3 WHERE rowid=3"         {{column a is}}
  7    "UPDATE t1 SET a = 'reveal' WHERE rowid=4"    {{column a is}}
  8    "UPDATE t1 SET a = X'123456' WHERE rowid=1"   {{column a is}}

  9    "INSERT INTO t4 VALUES('xyx', 1, 1)"          {{columns a, b, c are}}
  10   "INSERT INTO t4 VALUES('xyx', 2, 1)"          {{columns a, b, c are}}
  11   "INSERT INTO t4 VALUES('uvw', 1, 1)"          {{columns a, b, c are}}

  12   "UPDATE t4 SET a='xyx' WHERE rowid=3"         {{columns a, b, c are}}
  13   "UPDATE t4 SET b=1 WHERE rowid=2"             {{columns a, b, c are}}
  14   "UPDATE t4 SET a=0, b=0, c=0"                 {{columns a, b, c are}}
}

# EVIDENCE-OF: R-21289-11559 As with PRIMARY KEY constraints, for the
# purposes of UNIQUE constraints NULL values are considered distinct
# from all other values (including other NULLs).
#
do_createtable_tests 4.8 {
  1    "INSERT INTO t1 VALUES(NULL, NULL)"           {}
  2    "INSERT INTO t1 VALUES(NULL, NULL)"           {}
  3    "UPDATE t1 SET a = NULL"                      {}
  4    "UPDATE t1 SET b = NULL"                      {}

  5    "INSERT INTO t4 VALUES(NULL, NULL, NULL)"     {}
  6    "INSERT INTO t4 VALUES(NULL, NULL, NULL)"     {}
  7    "UPDATE t4 SET a = NULL"                      {}
  8    "UPDATE t4 SET b = NULL"                      {}
  9    "UPDATE t4 SET c = NULL"                      {}
}

# EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both
# UNIQUE and PRIMARY KEY constraints are implemented by creating an
# index in the database (in the same way as a "CREATE UNIQUE INDEX"
# statement would).
do_createtable_tests 4.9 -repair drop_all_tables -query {
  SELECT count(*) FROM sqlite_master WHERE type='index'
} {
  1    "CREATE TABLE t1(a TEXT PRIMARY KEY, b)"              1
  2    "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)"           0
  3    "CREATE TABLE t1(a TEXT UNIQUE, b)"                   1
  4    "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)"       2
  5    "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))"  2
}

# EVIDENCE-OF: R-02252-33116 Such an index is used like any other index
# in the database to optimize queries.
#
do_execsql_test 4.10.0 {
  CREATE TABLE t1(a, b PRIMARY KEY);
  CREATE TABLE t2(a, b, c, UNIQUE(b, c));
}
do_createtable_tests 4.10 {
  1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 
       {0 0 {TABLE t1 WITH INDEX sqlite_autoindex_t1_1}}

  2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
       {0 0 {TABLE t2 WITH INDEX sqlite_autoindex_t2_1 ORDER BY}}

  3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
       {0 0 {TABLE t2 WITH INDEX sqlite_autoindex_t2_1}}
}

# EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
# column definition or specified as a table constraint. In practice it
# makes no difference.
#
#   All the tests that deal with CHECK constraints below (4.11.* and 
#   4.12.*) are run once for a table with the check constraint attached
#   to a column definition, and once with a table where the check 
#   condition is specified as a table constraint.
#
# EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the
# table or an existing row is updated, the expression associated with
# each CHECK constraint is evaluated and cast to a NUMERIC value in the
# same way as a CAST expression. If the result is zero (integer value 0
# or real value 0.0), then a constraint violation has occurred.
#
drop_all_tables
do_execsql_test 4.11 {
  CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));
  CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));
  INSERT INTO x1 VALUES('x', 'xx');
  INSERT INTO x1 VALUES('y', 'yy');
  INSERT INTO t1 SELECT * FROM x1;

  CREATE TABLE x2(a CHECK( a||b ), b);
  CREATE TABLE t2(a, b, CHECK( a||b ));
  INSERT INTO x2 VALUES(1, 'xx');
  INSERT INTO x2 VALUES(1, 'yy');
  INSERT INTO t2 SELECT * FROM x2;
}

do_createtable_tests 4.11 -error {constraint failed} {
  1a    "INSERT INTO x1 VALUES('one', 0)"       {}
  1b    "INSERT INTO t1 VALUES('one', -4.0)"    {}

  2a    "INSERT INTO x2 VALUES('abc', 1)"       {}
  2b    "INSERT INTO t2 VALUES('abc', 1)"       {}

  3a    "INSERT INTO x2 VALUES(0, 'abc')"       {}
  3b    "INSERT INTO t2 VALUES(0, 'abc')"       {}

  4a    "UPDATE t1 SET b=-1 WHERE rowid=1"      {}
  4b    "UPDATE x1 SET b=-1 WHERE rowid=1"      {}

  4a    "UPDATE x2 SET a='' WHERE rowid=1"      {}
  4b    "UPDATE t2 SET a='' WHERE rowid=1"      {}
}

# EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,
# or any other non-zero value, it is not a constraint violation.
#
do_createtable_tests 4.12 {
  1a    "INSERT INTO x1 VALUES('one', NULL)"    {}
  1b    "INSERT INTO t1 VALUES('one', NULL)"    {}

  2a    "INSERT INTO x1 VALUES('one', 2)"    {}
  2b    "INSERT INTO t1 VALUES('one', 2)"    {}

  3a    "INSERT INTO x2 VALUES(1, 'abc')"       {}
  3b    "INSERT INTO t2 VALUES(1, 'abc')"       {}
}

finish_test
Changes to test/tester.tcl.
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
  if {[info exists ::testprefix] 
   && [string is digit [string range $testname 0 0]]
  } {
    set testname "${::testprefix}-$testname"
  }
}
    
proc do_execsql_test {testname sql result} {
  fix_testname testname
  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
}
proc do_catchsql_test {testname sql result} {
  fix_testname testname
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}







|







341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
  if {[info exists ::testprefix] 
   && [string is digit [string range $testname 0 0]]
  } {
    set testname "${::testprefix}-$testname"
  }
}
    
proc do_execsql_test {testname sql {result {}}} {
  fix_testname testname
  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
}
proc do_catchsql_test {testname sql result} {
  fix_testname testname
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}