Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add tests for table-constraints to e_createtable.test. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
70f511872e938e096537fb0ca097ccdd |
User & Date: | dan 2010-10-04 10:45:32.000 |
Context
2010-10-04
| ||
11:01 | Fix broken evidence marks in e_expr.test. (check-in: 37ff94cec8 user: dan tags: trunk) | |
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) | |
Changes
Changes to test/e_createtable.test.
︙ | ︙ | |||
1438 1439 1440 1441 1442 1443 1444 1445 1446 | 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 | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 | 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')" {} } # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached # to a column definition, not specified as a table constraint. # drop_all_tables do_createtable_tests 4.13.1 { 1 "CREATE TABLE t1(a NOT NULL, b)" {} 2 "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)" {} 3 "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)" {} } do_createtable_tests 4.13.2 -error { near "NOT": syntax error } { 1 "CREATE TABLE t4(a, b, NOT NULL(a))" {} 2 "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))" {} 3 "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))" {} } # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the # associated column may not contain a NULL value. Attempting to set the # column value to NULL when inserting a new row or updating an existing # one causes a constraint violation. # # These tests use the tables created by 4.13. # do_execsql_test 4.14.0 { INSERT INTO t1 VALUES('x', 'y'); INSERT INTO t1 VALUES('z', NULL); INSERT INTO t2 VALUES('x', 'y'); INSERT INTO t2 VALUES('z', NULL); INSERT INTO t3 VALUES('x', 'y', 'z'); INSERT INTO t3 VALUES(1, 2, 3); } do_createtable_tests 4.14 -error { %s may not be NULL } { 1 "INSERT INTO t1 VALUES(NULL, 'a')" {t1.a} 2 "INSERT INTO t2 VALUES(NULL, 'b')" {t2.a} 3 "INSERT INTO t3 VALUES('c', 'd', NULL)" {t3.c} 4 "INSERT INTO t3 VALUES('e', NULL, 'f')" {t3.b} 5 "INSERT INTO t3 VALUES(NULL, 'g', 'h')" {t3.a} } # EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL # constraints may be explicitly assigned a default conflict resolution # algorithm by including a conflict-clause in their definitions. # # Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE # # Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL # and UNIQUE constraints, respectively. # drop_all_tables do_execsql_test 4.15.0 { CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b); CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b); CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b); CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b); CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b); CREATE TABLE t1_xx(a PRIMARY KEY, b); INSERT INTO t1_ab VALUES(1, 'one'); INSERT INTO t1_ab VALUES(2, 'two'); INSERT INTO t1_ro SELECT * FROM t1_ab; INSERT INTO t1_ig SELECT * FROM t1_ab; INSERT INTO t1_fa SELECT * FROM t1_ab; INSERT INTO t1_re SELECT * FROM t1_ab; INSERT INTO t1_xx SELECT * FROM t1_ab; CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT); CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK); CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE); CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL); CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE); CREATE TABLE t2_xx(a, b NOT NULL); INSERT INTO t2_ab VALUES(1, 'one'); INSERT INTO t2_ab VALUES(2, 'two'); INSERT INTO t2_ro SELECT * FROM t2_ab; INSERT INTO t2_ig SELECT * FROM t2_ab; INSERT INTO t2_fa SELECT * FROM t2_ab; INSERT INTO t2_re SELECT * FROM t2_ab; INSERT INTO t2_xx SELECT * FROM t2_ab; CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT); CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK); CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE); CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL); CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE); CREATE TABLE t3_xx(a, b, UNIQUE(a, b)); INSERT INTO t3_ab VALUES(1, 'one'); INSERT INTO t3_ab VALUES(2, 'two'); INSERT INTO t3_ro SELECT * FROM t3_ab; INSERT INTO t3_ig SELECT * FROM t3_ab; INSERT INTO t3_fa SELECT * FROM t3_ab; INSERT INTO t3_re SELECT * FROM t3_ab; INSERT INTO t3_xx SELECT * FROM t3_ab; } foreach {tn tbl res ac data} { 1 t1_ab {1 {column a is not unique}} 0 {1 one 2 two 3 three} 2 t1_ro {1 {column a is not unique}} 1 {1 one 2 two} 3 t1_fa {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string} 4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string} 5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string} 6 t1_xx {1 {column a is not unique}} 0 {1 one 2 two 3 three} } { catchsql COMMIT do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" do_catchsql_test 4.15.$tn.2 " INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl; " $res do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data } foreach {tn tbl res ac data} { 1 t2_ab {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three} 2 t2_ro {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two} 3 t2_fa {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx} 4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx} 5 t2_re {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three} 6 t2_xx {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three} } { catchsql COMMIT do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" do_catchsql_test 4.16.$tn.2 " INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl " $res do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data } foreach {tn tbl res ac data} { 1 t3_ab {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three} 2 t3_ro {1 {columns a, b are not unique}} 1 {1 one 2 two} 3 t3_fa {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three} 4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three} 5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three} 6 t3_xx {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three} } { catchsql COMMIT do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" do_catchsql_test 4.17.$tn.2 " INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl " $res do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data } catchsql COMMIT # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not # include a conflict-clause or it is a CHECK constraint, the default # conflict resolution algorithm is ABORT. # # The first half of the above is tested along with explicit ON # CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx # and t3_xx). The following just tests that the default conflict # handling for CHECK constraints is ABORT. # do_execsql_test 4.18.1 { CREATE TABLE t4(a, b CHECK (b!=10)); INSERT INTO t4 VALUES(1, 2); INSERT INTO t4 VALUES(3, 4); } do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) } do_catchsql_test 4.18.3 { INSERT INTO t4 SELECT a+4, b+4 FROM t4 } {1 {constraint failed}} do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0 do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6} # EVIDENCE-OF: R-19114-56113 Different constraints within the same table # may have different default conflict resolution algorithms. # do_execsql_test 4.19.0 { CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT); } do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}} do_execsql_test 4.19.2 { SELECT * FROM t5 } {} do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \ {1 {t5.b may not be NULL}} do_execsql_test 4.19.4 { SELECT * FROM t5 } {} finish_test |