/ Check-in [6b4fcd81]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6b4fcd81e775ff4c5d7bc454378555cf73cf5772
User & Date: drh 2009-12-31 15:51:38
Context
2009-12-31
19:06
Changes to remove warnings in MSVC build. check-in: 6cf76c2a user: shaneh tags: trunk
15:51
Change evidence marks to track requirement number changes in the foreign-key documentation. check-in: 6b4fcd81 user: drh tags: trunk
2009-12-30
14:19
Change the version number to 3.6.22. check-in: 96919a46 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_fkey.test.

408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
...
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
...
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
....
1006
1007
1008
1009
1010
1011
1012
1013

1014
1015
1016
1017
1018
1019
1020
....
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
....
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
....
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
....
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
  }
} {}
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-17902-59250
#
# Test an example from foreignkeys.html.
#
drop_all_tables
do_test e_fkey-13.1 {
  execsql {
    CREATE TABLE artist(
................................................................................
    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-15034-64331
#
# 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';
  }
................................................................................
  }
}

#-------------------------------------------------------------------------
# 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-47109-40581 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);
................................................................................
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-54172-55848

#
# 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
#
................................................................................
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-35043-01546
#
# Test an example from foreignkeys.html dealing with a deferred foreign 
# key constraint.
#
do_test e_fkey-35.1 {
  drop_all_tables
  execsql {
................................................................................
  }
} {{} {} 4 11 5 10}
do_test e_fkey-46.5 {
  execsql { SELECT * FROM p1 }
} {{} 6 4 11 5 10}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-51329-33438
#
# 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 {
................................................................................
} {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-07065-59588
#
# 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"
................................................................................
  execsql {
    UPDATE zeus SET b = NULL;
    SELECT typeof(c), c, typeof(d), d FROM apollo;
  }
} {integer 1 null {}}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-58589-50781
#
# 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







|







 







|







 







|
|

|







 







|
>







 







|







 







|







 







|







 







|







408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
...
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
...
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
....
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
....
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
....
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
....
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
....
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
  }
} {}
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(
................................................................................
    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';
  }
................................................................................
  }
}

#-------------------------------------------------------------------------
# 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);
................................................................................
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
#
................................................................................
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 {
................................................................................
  }
} {{} {} 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 {
................................................................................
} {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"
................................................................................
  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