/ Check-in [a2ca9f1a]
Login
Home Timeline Branches Tags Tickets Wiki More...
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:Update e_fkey.test to match the latest version of foreignkeys.html.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a2ca9f1a7a158e7b83a642a8d17549d81caea557
User & Date: dan 2009-10-12 15:25:29
Context
2009-10-12
16:02
Fix some mappings from e_fkey.test. check-in: 171c6713 user: dan tags: trunk
15:25
Update e_fkey.test to match the latest version of foreignkeys.html. check-in: a2ca9f1a user: dan tags: trunk
11:27
Extra test cases mapped to statements in foreignkeys.html. check-in: ffa6207d user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_fkey.test. 

50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
...
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
...
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
...
745
746
747
748
749
750
751
752
753
754
755
756
757


758
759
760
761
762
763
764
...
767
768
769
770
771
772
773






774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790


791
792
793
794
795
796


797
798
799
800
801
802
803
804
805
806
807
808
809
810


811
812
813
814
815
816
817
818
819


820
821
822
823
824
825
826
827
828


829
830
831
832
833
834
835
836
837
838


839
840
841
842
843
844
845


#-------------------------------------------------------------------------
# /* EV: R-44697-61543 */
#
# Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY.
#
# /* EV: R-22567-44039 */
# /* EV: R-60444-29168 */
#
# Specifically, test that "PRAGMA foreign_keys" is a no-op in this case.
# When using the pragma to query the current setting, 0 rows are returned.
#
reset_db
ifcapable !trigger&&foreignkey {
  do_test e_fkey-51.1 {
................................................................................

#-------------------------------------------------------------------------
# /* EV: R-07280-60510 */
#
# Test that even if foreign keys are supported by the build, they must
# be enabled using "PRAGMA foreign_keys = ON" (or similar).
#
# /* EV: R-15831-45974 */
#
# This also tests that foreign key constraints are disabled by default.
#
drop_all_tables
do_test e_fkey-53.1 {
  execsql {
    CREATE TABLE p(i PRIMARY KEY);
................................................................................
  execsql { 
    PRAGMA foreign_keys = OFF;
    PRAGMA foreign_keys;
  }
} {0}

#-------------------------------------------------------------------------
# /* EV: R-07050-54503 */
#
# Test that it is not possible to enable or disable foreign key support
# while not in auto-commit mode.
#
reset_db
do_test e_fkey-55.1 {
  execsql {
................................................................................
# /* EV: R-12782-61841 */
#
# Test that an FK constraint is made deferred by adding the following
# to the definition:
#
#   DEFERRABLE INITIALLY DEFERRED
#
# /* EV: R-54882-46975 */
#
# Also test that adding any of the following to a foreign key definition 
# makes the constraint IMMEDIATE:
#
#   NOT DEFERRABLE INITIALLY DEFERRED


#   DEFERRABLE INITIALLY IMMEDIATE
#   DEFERRABLE
#
# /* EV: R-35290-16460 */
#
# Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT
# DEFERRABLE clause).
................................................................................
do_test e_fkey-29.1 {
  execsql {
    CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z));
    CREATE TABLE c1(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED
    );
    CREATE TABLE c2(a, b, c,






      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY IMMEDIATE
    );
    CREATE TABLE c3(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE
    );
    CREATE TABLE c4(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent);

    -- This FK constraint is the only deferrable one.
    CREATE TABLE c5(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY DEFERRED
    );

    INSERT INTO parent VALUES('a', 'b', 'c');
    INSERT INTO parent VALUES('d', 'e', 'f');
    INSERT INTO parent VALUES('g', 'h', 'i');
    INSERT INTO parent VALUES('j', 'k', 'l');
    INSERT INTO parent VALUES('m', 'n', 'o');



    INSERT INTO c1 VALUES('a', 'b', 'c');
    INSERT INTO c2 VALUES('d', 'e', 'f');
    INSERT INTO c3 VALUES('g', 'h', 'i');
    INSERT INTO c4 VALUES('j', 'k', 'l');
    INSERT INTO c5 VALUES('m', 'n', 'o');


  }
} {}

proc test_efkey_29 {tn sql isError} {
  do_test e_fkey-29.$tn "catchsql {$sql}" [
    lindex {{0 {}} {1 {foreign key constraint failed}}} $isError
  ]
}
test_efkey_29  2 "BEGIN"                                   0
test_efkey_29  3 "DELETE FROM parent WHERE x = 'a'"        1
test_efkey_29  4 "DELETE FROM parent WHERE x = 'd'"        1
test_efkey_29  5 "DELETE FROM parent WHERE x = 'g'"        1
test_efkey_29  6 "DELETE FROM parent WHERE x = 'j'"        1
test_efkey_29  7 "DELETE FROM parent WHERE x = 'm'"        0


test_efkey_29  8 "COMMIT"                                  1
test_efkey_29  9 "ROLLBACK"                                0

test_efkey_29  9 "BEGIN"                                   0
test_efkey_29 10 "UPDATE parent SET z = 'z' WHERE z = 'c'" 1
test_efkey_29 11 "UPDATE parent SET z = 'z' WHERE z = 'f'" 1
test_efkey_29 12 "UPDATE parent SET z = 'z' WHERE z = 'i'" 1
test_efkey_29 13 "UPDATE parent SET z = 'z' WHERE z = 'l'" 1
test_efkey_29 14 "UPDATE parent SET z = 'z' WHERE z = 'o'" 0


test_efkey_29 15 "COMMIT"                                  1
test_efkey_29 16 "ROLLBACK"                                0

test_efkey_29 17 "BEGIN"                                   0
test_efkey_29 18 "INSERT INTO c1 VALUES(1, 2, 3)"          1
test_efkey_29 19 "INSERT INTO c2 VALUES(1, 2, 3)"          1
test_efkey_29 20 "INSERT INTO c3 VALUES(1, 2, 3)"          1
test_efkey_29 21 "INSERT INTO c4 VALUES(1, 2, 3)"          1
test_efkey_29 22 "INSERT INTO c5 VALUES(1, 2, 3)"          0


test_efkey_29 23 "COMMIT"                                  1
test_efkey_29 24 "INSERT INTO parent VALUES(1, 2, 3)"      0
test_efkey_29 25 "COMMIT"                                  0

test_efkey_29 26 "BEGIN"                                   0
test_efkey_29 27 "UPDATE c1 SET a = 10"                    1
test_efkey_29 28 "UPDATE c2 SET a = 10"                    1
test_efkey_29 29 "UPDATE c3 SET a = 10"                    1
test_efkey_29 30 "UPDATE c4 SET a = 10"                    1
test_efkey_29 31 "UPDATE c5 SET a = 10"                    0


test_efkey_29 32 "COMMIT"                                  1
test_efkey_29 33 "ROLLBACK"                                0

#-------------------------------------------------------------------------
# /* EV: R-27340-26081 */
#
# Test an example from foreignkeys.html dealing with a deferred foreign 








|







 







|







 







|







 







|





>
>







 







>
>
>
>
>
>


|


|


|








>
>






>
>













|
>
>
|
|






|
>
>
|
|






|
>
>









|
>
>








50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
...
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
...
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
...
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
...
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865


#-------------------------------------------------------------------------
# /* EV: R-44697-61543 */
#
# Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY.
#
# /* EV: R-22567-44039 */
# /* EV: R-41784-13339 */
#
# Specifically, test that "PRAGMA foreign_keys" is a no-op in this case.
# When using the pragma to query the current setting, 0 rows are returned.
#
reset_db
ifcapable !trigger&&foreignkey {
  do_test e_fkey-51.1 {
................................................................................

#-------------------------------------------------------------------------
# /* EV: R-07280-60510 */
#
# Test that even if foreign keys are supported by the build, they must
# be enabled using "PRAGMA foreign_keys = ON" (or similar).
#
# /* EV: R-59578-04990 */
#
# This also tests that foreign key constraints are disabled by default.
#
drop_all_tables
do_test e_fkey-53.1 {
  execsql {
    CREATE TABLE p(i PRIMARY KEY);
................................................................................
  execsql { 
    PRAGMA foreign_keys = OFF;
    PRAGMA foreign_keys;
  }
} {0}

#-------------------------------------------------------------------------
# /* EV: R-46649-58537 */
#
# Test that it is not possible to enable or disable foreign key support
# while not in auto-commit mode.
#
reset_db
do_test e_fkey-55.1 {
  execsql {
................................................................................
# /* EV: R-12782-61841 */
#
# Test that an FK constraint is made deferred by adding the following
# to the definition:
#
#   DEFERRABLE INITIALLY DEFERRED
#
# /* EV: R-09005-28791 */
#
# Also test that adding any of the following to a foreign key definition 
# makes the constraint IMMEDIATE:
#
#   NOT DEFERRABLE INITIALLY DEFERRED
#   NOT DEFERRABLE INITIALLY IMMEDIATE
#   NOT DEFERRABLE
#   DEFERRABLE INITIALLY IMMEDIATE
#   DEFERRABLE
#
# /* EV: R-35290-16460 */
#
# Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT
# DEFERRABLE clause).
................................................................................
do_test e_fkey-29.1 {
  execsql {
    CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z));
    CREATE TABLE c1(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED
    );
    CREATE TABLE c2(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY IMMEDIATE
    );
    CREATE TABLE c3(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE
    );
    CREATE TABLE c4(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY IMMEDIATE
    );
    CREATE TABLE c5(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE
    );
    CREATE TABLE c6(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent);

    -- This FK constraint is the only deferrable one.
    CREATE TABLE c7(a, b, c,
      FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY DEFERRED
    );

    INSERT INTO parent VALUES('a', 'b', 'c');
    INSERT INTO parent VALUES('d', 'e', 'f');
    INSERT INTO parent VALUES('g', 'h', 'i');
    INSERT INTO parent VALUES('j', 'k', 'l');
    INSERT INTO parent VALUES('m', 'n', 'o');
    INSERT INTO parent VALUES('p', 'q', 'r');
    INSERT INTO parent VALUES('s', 't', 'u');

    INSERT INTO c1 VALUES('a', 'b', 'c');
    INSERT INTO c2 VALUES('d', 'e', 'f');
    INSERT INTO c3 VALUES('g', 'h', 'i');
    INSERT INTO c4 VALUES('j', 'k', 'l');
    INSERT INTO c5 VALUES('m', 'n', 'o');
    INSERT INTO c6 VALUES('p', 'q', 'r');
    INSERT INTO c7 VALUES('s', 't', 'u');
  }
} {}

proc test_efkey_29 {tn sql isError} {
  do_test e_fkey-29.$tn "catchsql {$sql}" [
    lindex {{0 {}} {1 {foreign key constraint failed}}} $isError
  ]
}
test_efkey_29  2 "BEGIN"                                   0
test_efkey_29  3 "DELETE FROM parent WHERE x = 'a'"        1
test_efkey_29  4 "DELETE FROM parent WHERE x = 'd'"        1
test_efkey_29  5 "DELETE FROM parent WHERE x = 'g'"        1
test_efkey_29  6 "DELETE FROM parent WHERE x = 'j'"        1
test_efkey_29  7 "DELETE FROM parent WHERE x = 'm'"        1
test_efkey_29  8 "DELETE FROM parent WHERE x = 'p'"        1
test_efkey_29  9 "DELETE FROM parent WHERE x = 's'"        0
test_efkey_29 10 "COMMIT"                                  1
test_efkey_29 11 "ROLLBACK"                                0

test_efkey_29  9 "BEGIN"                                   0
test_efkey_29 10 "UPDATE parent SET z = 'z' WHERE z = 'c'" 1
test_efkey_29 11 "UPDATE parent SET z = 'z' WHERE z = 'f'" 1
test_efkey_29 12 "UPDATE parent SET z = 'z' WHERE z = 'i'" 1
test_efkey_29 13 "UPDATE parent SET z = 'z' WHERE z = 'l'" 1
test_efkey_29 14 "UPDATE parent SET z = 'z' WHERE z = 'o'" 1
test_efkey_29 15 "UPDATE parent SET z = 'z' WHERE z = 'r'" 1
test_efkey_29 16 "UPDATE parent SET z = 'z' WHERE z = 'u'" 0
test_efkey_29 17 "COMMIT"                                  1
test_efkey_29 18 "ROLLBACK"                                0

test_efkey_29 17 "BEGIN"                                   0
test_efkey_29 18 "INSERT INTO c1 VALUES(1, 2, 3)"          1
test_efkey_29 19 "INSERT INTO c2 VALUES(1, 2, 3)"          1
test_efkey_29 20 "INSERT INTO c3 VALUES(1, 2, 3)"          1
test_efkey_29 21 "INSERT INTO c4 VALUES(1, 2, 3)"          1
test_efkey_29 22 "INSERT INTO c5 VALUES(1, 2, 3)"          1
test_efkey_29 22 "INSERT INTO c6 VALUES(1, 2, 3)"          1
test_efkey_29 22 "INSERT INTO c7 VALUES(1, 2, 3)"          0
test_efkey_29 23 "COMMIT"                                  1
test_efkey_29 24 "INSERT INTO parent VALUES(1, 2, 3)"      0
test_efkey_29 25 "COMMIT"                                  0

test_efkey_29 26 "BEGIN"                                   0
test_efkey_29 27 "UPDATE c1 SET a = 10"                    1
test_efkey_29 28 "UPDATE c2 SET a = 10"                    1
test_efkey_29 29 "UPDATE c3 SET a = 10"                    1
test_efkey_29 30 "UPDATE c4 SET a = 10"                    1
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

#-------------------------------------------------------------------------
# /* EV: R-27340-26081 */
#
# Test an example from foreignkeys.html dealing with a deferred foreign

This page was generated in about 0.009s by Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13