Lines Matching refs:parent
508 # columns are NULL, or there exists a row in the parent table for which
509 # each parent key column contains a value equal to the value in its
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
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
635 # specified in the CREATE TABLE statement for the parent table.
678 # child4 is an error because even though the parent key column is
684 # error because even though the parent key column has a unique index,
690 # because while both have UNIQUE indices on their parent keys, the keys
696 CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
697 CREATE UNIQUE INDEX i1 ON parent(c, d);
698 CREATE INDEX i2 ON parent(e);
699 CREATE UNIQUE INDEX i3 ON parent(f COLLATE nocase);
701 CREATE TABLE child1(f, g REFERENCES parent(a)); -- Ok
702 CREATE TABLE child2(h, i REFERENCES parent(b)); -- Ok
703 CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d)); -- Ok
704 CREATE TABLE child4(l, m REFERENCES parent(e)); -- Err
705 CREATE TABLE child5(n, o REFERENCES parent(f)); -- Err
706 CREATE TABLE child6(p, q, FOREIGN KEY(p,q) REFERENCES parent(b, c)); -- Err
707 CREATE TABLE child7(r REFERENCES parent(c)); -- Err
712 INSERT INTO parent VALUES(1, 2, 3, 4, 5, 6);
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"}}
743 # the child or parent tables in ways that use the foreign keys.
747 # be "no such table" if the parent table does not exist.
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
821 # mapping a child key to the primary key of the parent table when the
863 # * different number of child and parent key columns, and
868 # without having to consult the parent table definition, then the CREATE
898 # Test that a REFERENCING clause that does not specify parent key columns
899 # implicitly maps to the primary key of the parent table.
901 # EVIDENCE-OF: R-43879-08025 Attaching a "REFERENCES <parent-table>"
904 # <parent-table>.
943 CREATE TABLE parent(x, y, UNIQUE(y, x));
944 CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
945 CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
946 CREATE TABLE c3(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
958 test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)"
961 execsql "DELETE FROM $c ; DELETE FROM parent"
967 # Test an example showing that when a row is deleted from the parent
973 # then SQLite concludes that deleting the row from the parent table
1037 # Test that when a row is deleted from the parent table of an FK
1043 # Also test that when a row is inserted into the parent table, or when the
1044 # parent key values of an existing row are modified, a query equivalent
1051 # of the parent key is modified or a new row is inserted into the parent
1057 execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) }
1061 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y))
1064 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
1068 CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
1076 [eqp "DELETE FROM parent WHERE 1"] \
1080 [eqp "UPDATE parent SET x=?, y=?"] \
1086 do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete
1087 do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update
1097 # related operations on the parent table do not provoke linear scans.
1136 # Check that parent and child keys must have the same number of columns.
1225 # requirement for a corresponding row in the parent table.
1351 CREATE TABLE parent(x, y);
1352 CREATE UNIQUE INDEX pi ON parent(x, y);
1354 FOREIGN KEY(a, b) REFERENCES parent(x, y) DEFERRABLE INITIALLY DEFERRED
1359 test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')"
1394 CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z));
1396 FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED
1399 FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY IMMEDIATE
1402 FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE
1405 FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY IMMEDIATE
1408 FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE
1410 CREATE TABLE c6(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent);
1414 FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY DEFERRED
1417 INSERT INTO parent VALUES('a', 'b', 'c');
1418 INSERT INTO parent VALUES('d', 'e', 'f');
1419 INSERT INTO parent VALUES('g', 'h', 'i');
1420 INSERT INTO parent VALUES('j', 'k', 'l');
1421 INSERT INTO parent VALUES('m', 'n', 'o');
1422 INSERT INTO parent VALUES('p', 'q', 'r');
1423 INSERT INTO parent VALUES('s', 't', 'u');
1441 test_efkey_29 3 "DELETE FROM parent WHERE x = 'a'" 1
1442 test_efkey_29 4 "DELETE FROM parent WHERE x = 'd'" 1
1443 test_efkey_29 5 "DELETE FROM parent WHERE x = 'g'" 1
1444 test_efkey_29 6 "DELETE FROM parent WHERE x = 'j'" 1
1445 test_efkey_29 7 "DELETE FROM parent WHERE x = 'm'" 1
1446 test_efkey_29 8 "DELETE FROM parent WHERE x = 'p'" 1
1447 test_efkey_29 9 "DELETE FROM parent WHERE x = 's'" 0
1452 test_efkey_29 10 "UPDATE parent SET z = 'z' WHERE z = 'c'" 1
1453 test_efkey_29 11 "UPDATE parent SET z = 'z' WHERE z = 'f'" 1
1454 test_efkey_29 12 "UPDATE parent SET z = 'z' WHERE z = 'i'" 1
1455 test_efkey_29 13 "UPDATE parent SET z = 'z' WHERE z = 'l'" 1
1456 test_efkey_29 14 "UPDATE parent SET z = 'z' WHERE z = 'o'" 1
1457 test_efkey_29 15 "UPDATE parent SET z = 'z' WHERE z = 'r'" 1
1458 test_efkey_29 16 "UPDATE parent SET z = 'z' WHERE z = 'u'" 0
1471 test_efkey_29 24 "INSERT INTO parent VALUES(1, 2, 3)" 0
1666 # deleting or modifying rows of the parent table, respectively.
1670 # the parent table (ON DELETE), or modifying the parent key values of
1733 CREATE TABLE parent(x PRIMARY KEY, y);
1735 b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT
1738 b REFERENCES parent ON UPDATE RESTRICT ON DELETE SET NULL
1741 b REFERENCES parent ON UPDATE SET NULL ON DELETE SET DEFAULT
1744 b REFERENCES parent ON UPDATE SET DEFAULT ON DELETE CASCADE
1748 CREATE TABLE child5(a, b REFERENCES parent ON UPDATE CASCADE);
1749 CREATE TABLE child6(a, b REFERENCES parent ON DELETE RESTRICT);
1750 CREATE TABLE child7(a, b REFERENCES parent ON DELETE NO ACTION);
1751 CREATE TABLE child8(a, b REFERENCES parent ON UPDATE NO ACTION);
1756 2 child1 {0 0 parent b {} {NO ACTION} RESTRICT NONE}
1757 3 child2 {0 0 parent b {} RESTRICT {SET NULL} NONE}
1758 4 child3 {0 0 parent b {} {SET NULL} {SET DEFAULT} NONE}
1759 5 child4 {0 0 parent b {} {SET DEFAULT} CASCADE NONE}
1760 6 child5 {0 0 parent b {} CASCADE {NO ACTION} NONE}
1761 7 child6 {0 0 parent b {} {NO ACTION} RESTRICT NONE}
1762 8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
1763 9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
1770 # it's parent row is updated or deleted.
1773 # when a parent key is modified or deleted from the database, no special
1779 CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2));
1781 FOREIGN KEY(c1, c2) REFERENCES parent
1786 INSERT INTO parent VALUES('j', 'k');
1787 INSERT INTO parent VALUES('l', 'm');
1795 UPDATE parent SET p1='k' WHERE p1='j';
1796 DELETE FROM parent WHERE p1='l';
1809 # or updating a parent table row when there exists one or more child keys
1814 # modifying (for ON UPDATE RESTRICT) a parent key when there exists one
1820 CREATE TABLE parent(p1, p2);
1821 CREATE UNIQUE INDEX parent_i ON parent(p1, p2);
1823 FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON DELETE RESTRICT
1826 FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON UPDATE RESTRICT
1832 INSERT INTO parent VALUES('a', 'b');
1833 INSERT INTO parent VALUES('c', 'd');
1839 catchsql { DELETE FROM parent WHERE p1 = 'a' }
1842 catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' }
1860 CREATE TABLE parent(x PRIMARY KEY);
1861 CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT);
1862 CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION);
1864 INSERT INTO parent VALUES('key1');
1865 INSERT INTO parent VALUES('key2');
1869 CREATE TRIGGER parent_t AFTER UPDATE ON parent BEGIN
1876 catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
1880 UPDATE parent SET x = 'key two' WHERE x = 'key2';
1888 CREATE TABLE parent(x PRIMARY KEY);
1889 CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT);
1890 CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION);
1892 INSERT INTO parent VALUES('key1');
1893 INSERT INTO parent VALUES('key2');
1897 CREATE TRIGGER parent_t AFTER DELETE ON parent BEGIN
1904 catchsql { DELETE FROM parent WHERE x = 'key1' }
1908 DELETE FROM parent WHERE x = 'key2';
1916 CREATE TABLE parent(x PRIMARY KEY);
1917 CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT);
1918 CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION);
1920 INSERT INTO parent VALUES('key1');
1921 INSERT INTO parent VALUES('key2');
1927 catchsql { REPLACE INTO parent VALUES('key1') }
1931 REPLACE INTO parent VALUES('key2');
1941 # to return an error immediately if a parent key with dependent child
1947 CREATE TABLE parent(x PRIMARY KEY);
1948 CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT
1951 CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION
1955 INSERT INTO parent VALUES('key1');
1956 INSERT INTO parent VALUES('key2');
1963 catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' }
1966 execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' }
1981 CREATE TABLE parent(x PRIMARY KEY);
1982 CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT
1985 CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION
1989 INSERT INTO parent VALUES('key1');
1990 INSERT INTO parent VALUES('key2');
1997 catchsql { DELETE FROM parent WHERE x = 'key1' }
2000 execsql { DELETE FROM parent WHERE x = 'key2' }
2016 # then when a parent key is deleted (for ON DELETE SET NULL) or modified
2018 # child table that mapped to the parent key are set to contain SQL NULL
2101 # update operation on the parent key to each dependent child key.
2105 # deleted parent row is also deleted.
2143 # match the new parent key values.
2146 # update operation on the parent key to each dependent child key.
2234 CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a));
2236 FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT
2239 INSERT INTO parent VALUES('A', 'b', 'c');
2240 INSERT INTO parent VALUES('ONE', 'two', 'three');
2247 UPDATE parent SET a = '' WHERE a = 'oNe';
2254 DELETE FROM parent WHERE a = 'A';
2255 SELECT * FROM parent;
2259 catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' }
2272 # action is configured, but there is no row in the parent table that
2274 # parent key while dependent child keys exist still causes a foreign key
2312 # Check that the order of steps in an UPDATE or DELETE on a parent
2317 # 3. Update or delete the row in the parent table,
2323 proc maxparent {args} { db one {SELECT max(x) FROM parent} }
2327 CREATE TABLE parent(x PRIMARY KEY);
2329 CREATE TRIGGER bu BEFORE UPDATE ON parent BEGIN
2330 INSERT INTO parent VALUES(new.x-old.x);
2333 a DEFAULT (maxparent()) REFERENCES parent ON UPDATE SET DEFAULT
2335 CREATE TRIGGER au AFTER UPDATE ON parent BEGIN
2336 INSERT INTO parent VALUES(new.x+old.x);
2339 INSERT INTO parent VALUES(1);
2345 UPDATE parent SET x = 22;
2346 SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
2352 DELETE FROM parent;
2353 INSERT INTO parent VALUES(-1);
2355 UPDATE parent SET x = 22;
2356 SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
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
2428 CREATE TABLE parent(x PRIMARY KEY);
2429 CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL);
2430 INSERT INTO parent VALUES('key');
2436 UPDATE parent SET x = 'key';
2442 UPDATE parent SET x = 'key2';
2452 # Test that parent keys are not checked when tables are created.
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
2462 # Child keys are checked to ensure all component columns exist. If parent
2464 # are the same number of columns in the child and parent keys. (TODO: This
2523 # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table
2527 # is used to rename a table that is the parent table of one or more
2529 # constraints are modified to refer to the parent table by its new name
2535 # to reflect the new parent table name.
2541 CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b));
2543 CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
2544 CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);
2545 …CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES 'p 1 "parent one"' ON UPDATE CASCA…
2547 INSERT INTO 'p 1 "parent one"' VALUES(1, 1);
2556 execsql { ALTER TABLE 'p 1 "parent one"' RENAME TO p }
2770 # 2. Modifying foreign key definitions when a parent table is RENAMEd.
2797 ALTER TABLE p RENAME TO parent;
2801 } {{CREATE TABLE c(b REFERENCES "parent"(a))}}
2806 ALTER TABLE p RENAME TO parent;
2867 # parent key values, so long as at least one field of the child key is