Lines Matching refs:key
17 # deals with enabling/disabling foreign key support, is tested first,
19 # with builds that do not include complete foreign key support (because
51 # EVIDENCE-OF: R-37672-59189 In order to use foreign key constraints in
74 # version 3.6.19 (2009-10-14) - foreign key definitions are parsed and
75 # may be queried using PRAGMA foreign_key_list, but foreign key
116 # foreign key definitions cannot even be parsed (attempting to specify a
117 # foreign key definition is a syntax error).
119 # Specifically, test that foreign key constraints cannot even be parsed
150 # foreign key constraints enabled, it must still be enabled by the
153 # This also tests that foreign key constraints are disabled by default.
155 # EVIDENCE-OF: R-44261-39702 Foreign key constraints are disabled by
210 # Test that it is not possible to enable or disable foreign key support
214 # foreign key constraints in the middle of a multi-statement transaction
258 # EVIDENCE-OF: R-04042-24825 To do so, a foreign key definition may be
313 # If the foreign key column (trackartist) in table 'track' is set to NULL,
316 # EVIDENCE-OF: R-23980-48859 There is one exception: if the foreign key
506 # EVIDENCE-OF: R-56032-24923 The foreign key constraint is satisfied if
507 # for each row in the child table either one or more of the child key
509 # each parent key column contains a value equal to the value in its
510 # associated child key column.
513 # is a matching row in the parent table of a foreign key constraint.
554 # Specifically, test that when comparing child and parent key values the
555 # default collation sequence of the parent key column is used.
558 # sequence associated with the parent key column is always used.
584 # Specifically, test that when comparing child and parent key values the
585 # affinity of the parent key column is applied to the child key value
588 # EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key
589 # column has an affinity, then that affinity is applied to the child key
620 # A parent key must be either a PRIMARY KEY, subject to a UNIQUE
623 # EVIDENCE-OF: R-13435-26311 Usually, the parent key of a foreign key
624 # constraint is the primary key of the parent table. If they are not the
625 # primary key, then the parent key columns must be collectively subject
628 # Also test that if a parent key is not subject to a PRIMARY KEY or UNIQUE
630 # must use the default collation sequences associated with the parent key
633 # EVIDENCE-OF: R-00376-39212 If the parent key columns have a UNIQUE
648 } [lindex {{0 {}} {/1 {foreign key mismatch - ".*" referencing ".*"}/}} \
677 # EVIDENCE-OF: R-51039-44840 The foreign key declared as part of table
678 # child4 is an error because even though the parent key column is
683 # EVIDENCE-OF: R-01060-48788 The foreign key for table child5 is an
684 # error because even though the parent key column has a unique index,
720 } {1 {foreign key mismatch - "child4" referencing "parent"}}
723 } {1 {foreign key mismatch - "child5" referencing "parent"}}
726 } {1 {foreign key mismatch - "child6" referencing "parent"}}
729 } {1 {foreign key mismatch - "child7" referencing "parent"}}
734 # either "foreign key mismatch" or "no such table*" (using [string match]).
736 # EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
746 # foreign key DML errors is usually "foreign key mismatch" but can also
749 # EVIDENCE-OF: R-35763-48267 Foreign key DML errors are reported if: The
750 # parent table does not exist, or The parent key columns named in the
751 # foreign key constraint do not exist, or The parent key columns named
752 # in the foreign key constraint are not the primary key of the parent
755 # the primary key of the parent without specifying the primary key
756 # columns and the number of primary key columns in the parent do not
757 # match the number of child key columns.
787 3 c2 p2 "foreign key mismatch - \"c2\" referencing \"p2\""
788 4 c3 p3 "foreign key mismatch - \"c3\" referencing \"p3\""
789 5 c4 p4 "foreign key mismatch - \"c4\" referencing \"p4\""
790 6 c5 p5 "foreign key mismatch - \"c5\" referencing \"p5\""
791 7 c6 p6 "foreign key mismatch - \"c6\" referencing \"p6\""
792 8 c7 p7 "foreign key mismatch - \"c7\" referencing \"p7\""
820 # Test the example of foreign key mismatch errors caused by implicitly
821 # mapping a child key to the primary key of the parent table when the
822 # child key consists of a different number of columns to that primary key.
842 } {1 {foreign key mismatch - "child9" referencing "parent2"}}
845 } {1 {foreign key mismatch - "child9" referencing "parent2"}}
848 } {1 {foreign key mismatch - "child9" referencing "parent2"}}
851 } {1 {foreign key mismatch - "child10" referencing "parent2"}}
854 } {1 {foreign key mismatch - "child10" referencing "parent2"}}
857 } {1 {foreign key mismatch - "child10" referencing "parent2"}}
863 # * different number of child and parent key columns, and
866 # EVIDENCE-OF: R-23682-59820 By contrast, if foreign key errors can be
873 # EVIDENCE-OF: R-33883-28833 Foreign key DDL errors are reported
874 # regardless of whether or not foreign key constraints are enabled when
883 … {number of columns in foreign key does not match the number of columns in the referenced table}
885 … {number of columns in foreign key does not match the number of columns in the referenced table}
887 {unknown column "c" in foreign key definition}
889 {unknown column "c" in foreign key definition}
898 # Test that a REFERENCING clause that does not specify parent key columns
899 # implicitly maps to the primary key of the parent table.
903 # key constraint that maps the column to the primary key of
928 # Test that an index on on the child key columns of an FK constraint
931 # EVIDENCE-OF: R-15417-28014 Indices are not required for child key
934 # Also test that if an index is created on the child key columns, it does
937 # EVIDENCE-OF: R-15741-50893 The child key index does not have to be
974 # would violate the foreign key constraint and returns an error.
1035 # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
1041 # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
1044 # parent key values of an existing row are modified, a query equivalent
1048 # SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
1051 # of the parent key is modified or a new row is inserted into the parent
1096 # is "efficient". In this case "efficient" means that foreign key
1143 {foreign key on jj should reference only one column of table p}
1148 {number of columns in foreign key does not match the number of columns in the referenced table}
1157 {number of columns in foreign key does not match the number of columns in the referenced table}
1160 {number of columns in foreign key does not match the number of columns in the referenced table}
1172 } {1 {foreign key mismatch - "c" referencing "p"}}
1180 } {1 {foreign key mismatch - "c" referencing "p"}}
1223 # EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns
1245 # database so that an immediate foreign key constraint is in violation
1300 # contents of the database such that a deferred foreign key constraint
1303 # EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not
1308 # any number of deferred foreign key constraints.
1311 # foreign key constraints remain in violation.
1373 # Also test that adding any of the following to a foreign key definition
1385 # EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by
1388 # EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is
1489 # key constraint.
1521 # deferred foreign key constraints.
1525 # deferred foreign key constraint.
1560 # satisfying deferred foreign key constraints. It may be rolled back.
1601 # Test that if a COMMIT operation fails due to deferred foreign key
1606 # state that violates a deferred foreign key constraint and there are
1668 # EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses
1670 # the parent table (ON DELETE), or modifying the parent key values of
1676 # EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have
1718 # Each foreign key in the system has an ON UPDATE and ON DELETE action,
1722 # associated with each foreign key in an SQLite database is one of "NO
1773 # when a parent key is modified or deleted from the database, no special
1814 # modifying (for ON UPDATE RESTRICT) a parent key when there exists one
1851 # RESTRICT action and normal foreign key constraint enforcement is that
1876 catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
1880 UPDATE parent SET x = 'key two' WHERE x = 'key2';
1883 } {{key two}}
1939 # EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is
1941 # to return an error immediately if a parent key with dependent child
1963 catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
1966 execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' }
1973 UPDATE child2 SET c = 'key two';
2016 # then when a parent key is deleted (for ON DELETE SET NULL) or modified
2017 # (for ON UPDATE SET NULL), the child key columns of all rows in the
2018 # child table that mapped to the parent key are set to contain SQL NULL
2059 # "SET NULL", except that each of the child key columns is set to
2101 # update operation on the parent key to each dependent child key.
2142 # that the values stored in each dependent child key are modified to
2143 # match the new parent key values.
2146 # update operation on the parent key to each dependent child key.
2225 # requirement not to violate the foreign key constraint.
2228 # action does not mean that the foreign key constraint does not need to
2268 # clause does not abrogate the need to satisfy the foreign key constraint
2273 # corresponds to the default values of the child key columns, deleting a
2274 # parent key while dependent child keys exist still causes a foreign key
2316 # 2. Check local (non foreign key) constraints,
2318 # 4. Perform any required foreign key actions,
2362 # Verify that ON UPDATE actions only actually take place if the parent key
2368 # values of the parent key are modified so that the new parent key
2422 # only take place if at least one parent key column is set to a value
2430 INSERT INTO parent VALUES('key');
2431 INSERT INTO child VALUES('key');
2436 UPDATE parent SET x = 'key';
2439 } {key}
2454 # EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key
2458 # creating a foreign key definition that refers to a parent table that
2459 # does not exist, or to parent key columns that do not exist or are not
2463 # key columns are explicitly specified, SQLite checks to make sure there
2469 # or not foreign key constraints are enabled."
2472 # whether or not foreign key constraints are enabled.
2484 {1 {unknown column "c" in foreign key definition}}
2486 …{1 {number of columns in foreign key does not match the number of columns in the referenced table}}
2528 # foreign key constraints, the definitions of the foreign key
2579 # cause any triggers to fire, but does fire foreign key actions.
2581 # EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when
2586 # triggers to fire, but may invoke foreign key actions or constraint
2649 # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the
2652 # EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is
2682 # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting
2685 # EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is
2687 # the transaction if the foreign key constraint violations still exist
2717 # Any "foreign key mismatch" errors encountered while running an implicit
2720 # EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors
2754 } {1 {foreign key mismatch - "c2" referencing "p"}}
2758 } {1 {foreign key mismatch - "c2" referencing "p"}}
2770 # 2. Modifying foreign key definitions when a parent table is RENAMEd.
2845 # Test that MATCH clauses are parsed, but SQLite treats every foreign key
2851 # EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are
2865 # MATCH SIMPLE behavior: Allow any child key that contains one or more
2867 # parent key values, so long as at least one field of the child key is
2875 # in the child key columns.
2884 # EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is
2922 # Test that the maximum recursion depth of foreign key action programs is
2929 # foreign key actions are considered trigger programs.
3016 # key actions.
3019 # not affect the operation of foreign key actions.