Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Change evidence marks to track requirement number changes in the foreign-key documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6b4fcd81e775ff4c5d7bc454378555cf |
User & Date: | drh 2009-12-31 15:51:38.000 |
Context
2009-12-31
| ||
19:06 | Changes to remove warnings in MSVC build. (check-in: 6cf76c2ae2 user: shaneh tags: trunk) | |
15:51 | Change evidence marks to track requirement number changes in the foreign-key documentation. (check-in: 6b4fcd81e7 user: drh tags: trunk) | |
2009-12-30
| ||
14:19 | Change the version number to 3.6.22. (check-in: 96919a46bb user: drh tags: trunk) | |
Changes
Changes to test/e_fkey.test.
︙ | ︙ | |||
408 409 410 411 412 413 414 | } } {} do_test e_fkey-12.2 { catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } } {1 {track.trackartist may not be NULL}} #------------------------------------------------------------------------- | | | 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 | } } {} do_test e_fkey-12.2 { catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } } {1 {track.trackartist may not be NULL}} #------------------------------------------------------------------------- # EVIDENCE-OF: R-16127-35442 # # Test an example from foreignkeys.html. # drop_all_tables do_test e_fkey-13.1 { execsql { CREATE TABLE artist( |
︙ | ︙ | |||
452 453 454 455 456 457 458 | INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; INSERT INTO track VALUES(15, 'Boogie Woogie', 3); } } {} #------------------------------------------------------------------------- | | | 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 | INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; INSERT INTO track VALUES(15, 'Boogie Woogie', 3); } } {} #------------------------------------------------------------------------- # EVIDENCE-OF: R-15958-50233 # # Test the second example from the first section of foreignkeys.html. # do_test e_fkey-14.1 { catchsql { DELETE FROM artist WHERE artistname = 'Frank Sinatra'; } |
︙ | ︙ | |||
875 876 877 878 879 880 881 | } } #------------------------------------------------------------------------- # Test that a REFERENCING clause that does not specify parent key columns # implicitly maps to the primary key of the parent table. # | | | | | 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 | } } #------------------------------------------------------------------------- # Test that a REFERENCING clause that does not specify parent key columns # implicitly maps to the primary key of the parent table. # # EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES <parent-table>" # clause to a column definition creates a foreign # key constraint that maps the column to the primary key of # <parent-table>. # do_test e_fkey-23.1 { execsql { CREATE TABLE p1(a, b, PRIMARY KEY(a, b)); CREATE TABLE p2(a, b PRIMARY KEY); CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p1); CREATE TABLE c2(a, b REFERENCES p2); |
︙ | ︙ | |||
1006 1007 1008 1009 1010 1011 1012 | do_test e_fkey-25.7 { concat \ [execsql { SELECT rowid FROM track WHERE trackartist = 6 }] \ [catchsql { DELETE FROM artist WHERE artistid = 6 }] } {2 1 {foreign key constraint failed}} #------------------------------------------------------------------------- | | > | 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 | do_test e_fkey-25.7 { concat \ [execsql { SELECT rowid FROM track WHERE trackartist = 6 }] \ [catchsql { DELETE FROM artist WHERE artistid = 6 }] } {2 1 {foreign key constraint failed}} #------------------------------------------------------------------------- # EVIDENCE-OF: R-47936-10044 Or, more generally: # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value # # Test that when a row is deleted from the parent table of an FK # constraint, the child table is queried for orphaned rows. The # query is equivalent to: # # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value # |
︙ | ︙ | |||
1458 1459 1460 1461 1462 1463 1464 | test_efkey_29 31 "UPDATE c5 SET a = 10" 1 test_efkey_29 31 "UPDATE c6 SET a = 10" 1 test_efkey_29 31 "UPDATE c7 SET a = 10" 0 test_efkey_29 32 "COMMIT" 1 test_efkey_29 33 "ROLLBACK" 0 #------------------------------------------------------------------------- | | | 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 | test_efkey_29 31 "UPDATE c5 SET a = 10" 1 test_efkey_29 31 "UPDATE c6 SET a = 10" 1 test_efkey_29 31 "UPDATE c7 SET a = 10" 0 test_efkey_29 32 "COMMIT" 1 test_efkey_29 33 "ROLLBACK" 0 #------------------------------------------------------------------------- # EVIDENCE-OF: R-24499-57071 # # Test an example from foreignkeys.html dealing with a deferred foreign # key constraint. # do_test e_fkey-35.1 { drop_all_tables execsql { |
︙ | ︙ | |||
2157 2158 2159 2160 2161 2162 2163 | } } {{} {} 4 11 5 10} do_test e_fkey-46.5 { execsql { SELECT * FROM p1 } } {{} 6 4 11 5 10} #------------------------------------------------------------------------- | | | 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 | } } {{} {} 4 11 5 10} do_test e_fkey-46.5 { execsql { SELECT * FROM p1 } } {{} 6 4 11 5 10} #------------------------------------------------------------------------- # EVIDENCE-OF: R-65058-57158 # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. # drop_all_tables do_test e_fkey-48.1 { execsql { |
︙ | ︙ | |||
2236 2237 2238 2239 2240 2241 2242 | } {ONE two three} do_test e_fkey-49.4 { catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- | | | 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 | } {ONE two three} do_test e_fkey-49.4 { catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # EVIDENCE-OF: R-11856-19836 # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT" # clause does not abrogate the need to satisfy the foreign key constraint # (R-28220-46694). # # EVIDENCE-OF: R-28220-46694 For example, if an "ON DELETE SET DEFAULT" |
︙ | ︙ | |||
2390 2391 2392 2393 2394 2395 2396 | execsql { UPDATE zeus SET b = NULL; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 null {}} #------------------------------------------------------------------------- | | | 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 | execsql { UPDATE zeus SET b = NULL; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 null {}} #------------------------------------------------------------------------- # EVIDENCE-OF: R-35129-58141 # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. This example demonstrates that ON UPDATE actions # only take place if at least one parent key column is set to a value # that is distinct from its previous value. # drop_all_tables |
︙ | ︙ |