SQLite

#-------------------------------------------------------------------------
# Test the effects of defining OMIT_FOREIGN_KEY.
#
# EVIDENCE-OF: R-58428-36660 If OMIT_FOREIGN_KEY is defined, then
# foreign key definitions cannot even be parsed (attempting to specify a
# foreign key definition is a syntax error).
#


# Specifically, test that foreign key constraints cannot even be parsed 
# in such a build.
#
reset_db
ifcapable !foreignkey {
  do_test e_fkey-3.1 {
    execsql { CREATE TABLE p(i PRIMARY KEY) }

  execsql { 
    UPDATE track SET trackartist = NULL WHERE trackid = 1;
    DELETE FROM artist WHERE artistid = 5;
  }
} {}

#-------------------------------------------------------------------------


# Test that the following is true fo all rows in the track table:
#
#   trackartist IS NULL OR 
#   EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist)
#
# EVIDENCE-OF: R-52486-21352 Expressed in SQL, this means that for every
# row in the track table, the following expression evaluates to true:

  set t   [expr int(rand()*50)]
  set sql [subst [lindex $Template [expr int(rand()*6)]]]

  test_r52486_21352 $i $sql
}

#-------------------------------------------------------------------------


# Check that a NOT NULL constraint can be added to the example schema
# to prohibit NULL child keys from being inserted.
#
# EVIDENCE-OF: R-42412-59321 Tip: If the application requires a stricter
# relationship between artist and track, where NULL values are not
# permitted in the trackartist column, simply add the appropriate "NOT
# NULL" constraint to the schema.

    do_test e_fkey-22.$fk.[incr i] {
      catchsql $sql
    } [list 1 $error]
  }
}

#-------------------------------------------------------------------------


# 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);

#-------------------------------------------------------------------------
# Test that an index on on the child key columns of an FK constraint
# is optional.
#
# EVIDENCE-OF: R-15417-28014 Indices are not required for child key
# columns
#


# Also test that if an index is created on the child key columns, it does
# not make a difference whether or not it is a UNIQUE index.
#
# EVIDENCE-OF: R-15741-50893 The child key index does not have to be
# (and usually will not be) a UNIQUE index.
#
drop_all_tables
do_test e_fkey-24.1 {
  execsql {
    CREATE TABLE parent(x, y, UNIQUE(y, x));
    CREATE TABLE c1(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));
    CREATE TABLE c2(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y));

#
# 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
#


# Also test that when a row is inserted into the parent table, or when the 
# parent key values of an existing row are modified, a query equivalent
# to the following is planned. In some cases it is not executed, but it
# is always planned.
#
#   SELECT rowid FROM <child-table> WHERE <child-key> = :parent_key_value
#

  do_test e_fkey-26.$tn.1 { eqp "DELETE FROM parent WHERE 1" } $delete
  do_test e_fkey-26.$tn.2 { eqp "UPDATE parent set x=?, y=?" } $update

  execsql {DROP TABLE child}
}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-14553-34013
#
# Test the example schema at the end of section 3. Also test that is
# is "efficient". In this case "efficient" means that foreign key
# related operations on the parent table do not provoke linear scans.
#
drop_all_tables
do_test e_fkey-27.1 {

###########################################################################
### SECTION 4.1: Composite Foreign Key Constraints
###########################################################################

#-------------------------------------------------------------------------
# Check that parent and child keys must have the same number of columns.
#
# EVIDENCE-OF: R-41062-34431 Parent and child keys must have the same
# cardinality.
#
foreach {tn sql err} {
  1 "CREATE TABLE c(jj REFERENCES p(x, y))" 
    {foreign key on jj should reference only one column of table p}

  2 "CREATE TABLE c(jj REFERENCES p())" {near ")": syntax error}

    CREATE TABLE c(a REFERENCES p);
  }
  catchsql {DELETE FROM p}
} {1 {foreign key mismatch}}


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-24676-09859
#
# Test the example schema in the "Composite Foreign Key Constraints" 
# section.
#
do_test e_fkey-29.1 {
  execsql {
    CREATE TABLE album(

  catchsql {
    INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever');
  }
} {1 {foreign key constraint failed}}


#-------------------------------------------------------------------------


# EVIDENCE-OF: R-33626-48418 In SQLite, if any of the child key columns
# (in this case songartist and songalbum) are NULL, then there is no
# requirement for a corresponding row in the parent table.
#
do_test e_fkey-30.1 {
  execsql {
    INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever');
    INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy');
  }
} {}

###########################################################################
### SECTION 4.2: Deferred Foreign Key Constraints
###########################################################################

#-------------------------------------------------------------------------







# Test that if a statement violates an immediate FK constraint, and the
# database does not satisfy the FK constraint once all effects of the
# statement have been applied, an error is reported and the effects of
# the statement rolled back.
#
# EVIDENCE-OF: R-09323-30470 If a statement modifies the contents of the
# database so that an immediate foreign key constraint is in violation
# at the conclusion the statement, an exception is thrown and the
# effects of the statement are reverted.
#
drop_all_tables
do_test e_fkey-31.1 {
  execsql {
    CREATE TABLE king(a, b, PRIMARY KEY(a));
    CREATE TABLE prince(c REFERENCES king, d);
  }

  execsql {
    COMMIT;
    SELECT * FROM king;
  }
} {1 {} 2 {}}

#-------------------------------------------------------------------------





# Test that if a deferred constraint is violated within a transaction,
# nothing happens immediately and the database is allowed to persist
# in a state that does not satisfy the FK constraint. However attempts
# to COMMIT the transaction fail until the FK constraint is satisfied.
#
# EVIDENCE-OF: R-49178-21358 By contrast, if a statement modifies the
# contents of the database such that a deferred foreign key constraint
# is violated, the violation is not reported immediately.
#
# EVIDENCE-OF: R-39692-12488 Deferred foreign key constraints are not
# checked until the transaction tries to COMMIT.
#
# EVIDENCE-OF: R-55147-47664 For as long as the user has an open
# transaction, the database is allowed to exist in a state that violates
# any number of deferred foreign key constraints.
#
# EVIDENCE-OF: R-29604-30395 However, COMMIT will fail as long as
# foreign key constraints remain in violation.
#
proc test_efkey_34 {tn isError sql} {
  do_test e_fkey-32.$tn "
    catchsql {$sql}
  " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
}
drop_all_tables

test_efkey_34  5 1 "COMMIT"
test_efkey_34  6 0   "INSERT INTO ll VALUES(10)"
test_efkey_34  7 1 "COMMIT"
test_efkey_34  8 0   "INSERT INTO ll VALUES(5)"
test_efkey_34  9 0 "COMMIT"

#-------------------------------------------------------------------------


# When not running inside a transaction, a deferred constraint is similar
# to an immediate constraint (violations are reported immediately).
#
# EVIDENCE-OF: R-56844-61705 If the current statement is not inside an
# explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit
# transaction is committed as soon as the statement has finished
# executing. In this case deferred constraints behave the same as
# immediate constraints.
#
drop_all_tables
proc test_efkey_35 {tn isError sql} {
  do_test e_fkey-33.$tn "
    catchsql {$sql}
  " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError]
}

} {}
test_efkey_35 2 1 "INSERT INTO child  VALUES('x', 'y')"
test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')"
test_efkey_35 4 0 "INSERT INTO child  VALUES('x', 'y')"


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-12782-61841
#
# Test that an FK constraint is made deferred by adding the following
# to the definition:
#
#   DEFERRABLE INITIALLY DEFERRED
#
# EVIDENCE-OF: 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
#


# Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT
# DEFERRABLE clause).
#
# EVIDENCE-OF: R-35290-16460 Foreign key constraints are immediate by
# default.
#
# EVIDENCE-OF: R-30323-21917 Each foreign key constraint in SQLite is
# classified as either immediate or deferred.
#
drop_all_tables
do_test e_fkey-34.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

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 {

  execsql {
    INSERT INTO artist VALUES(5, 'Bing Crosby');
    COMMIT;
  }
} {}

#-------------------------------------------------------------------------
# Verify that a nested savepoint may be released without satisfying 
# deferred foreign key constraints.
#
# EVIDENCE-OF: R-07223-48323 A nested savepoint transaction may be
# RELEASEd while the database is in a state that does not satisfy a
# deferred foreign key constraint.
#
drop_all_tables
do_test e_fkey-36.1 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY,
      b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED
    );

    UPDATE t1 SET a = 5 WHERE a = 4;
    COMMIT;
  }
} {}


#-------------------------------------------------------------------------


# Check that a transaction savepoint (an outermost savepoint opened when
# the database was in auto-commit mode) cannot be released without
# satisfying deferred foreign key constraints. It may be rolled back.
#
# EVIDENCE-OF: R-44295-13823 A transaction savepoint (a non-nested
# savepoint that was opened while there was not currently an open
# transaction), on the other hand, is subject to the same restrictions
# as a COMMIT - attempting to RELEASE it while the database is in such a
# state will fail.
#
do_test e_fkey-37.1 {
  execsql {
    SAVEPOINT one;
      SAVEPOINT two;
        INSERT INTO t1 VALUES(6, 7);
      RELEASE two;

  catchsql {RELEASE one}
} {1 {foreign key constraint failed}}
do_test e_fkey-37.6 {
  execsql {ROLLBACK TO one ; RELEASE one}
} {}

#-------------------------------------------------------------------------


# Test that if a COMMIT operation fails due to deferred foreign key 
# constraints, any nested savepoints remain open.
#
# EVIDENCE-OF: R-37736-42616 If a COMMIT statement (or the RELEASE of a
# transaction SAVEPOINT) fails because the database is currently in a
# state that violates a deferred foreign key constraint and there are
# currently nested savepoints, the nested savepoints remain open.
#
do_test e_fkey-38.1 {
  execsql {
    DELETE FROM t1 WHERE a>3;
    SELECT * FROM t1;
  }
} {1 1 2 2 3 3}

} {1 1 2 2 3 3 4 4 5 5}

###########################################################################
### SECTION 4.3: ON DELETE and ON UPDATE Actions
###########################################################################

#-------------------------------------------------------------------------


# Test that configured ON DELETE and ON UPDATE actions take place when
# deleting or modifying rows of the parent table, respectively.
#
# EVIDENCE-OF: R-48270-44282 Foreign key ON DELETE and ON UPDATE clauses
# are used to configure actions that take place when deleting rows from
# the parent table (ON DELETE), or modifying the parent key values of
# existing rows (ON UPDATE).
#
# Test that a single FK constraint may have different actions configured
# for ON DELETE and ON UPDATE.
#
# EVIDENCE-OF: R-48124-63225 A single foreign key constraint may have
# different actions configured for ON DELETE and ON UPDATE.
#
do_test e_fkey-39.1 {
  execsql {
    CREATE TABLE p(a, b PRIMARY KEY, c);
    CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p 
      ON UPDATE SET DEFAULT
      ON DELETE SET NULL

    CREATE UNIQUE INDEX pi ON p(c);
    REPLACE INTO p VALUES(5, 'k5', 'III');
    SELECT * FROM c1;
  }
} {1 xx k0 2 xx {} 3 xx {}}

#-------------------------------------------------------------------------


# Each foreign key in the system has an ON UPDATE and ON DELETE action,
# either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
#
# EVIDENCE-OF: R-33326-45252 The ON DELETE and ON UPDATE action
# associated with each foreign key in an SQLite database is one of "NO
# ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE".
#
# If none is specified explicitly, "NO ACTION" is the default.
#
# EVIDENCE-OF: R-19803-45884 If an action is not explicitly specified,
# it defaults to "NO ACTION".
# 
drop_all_tables
do_test e_fkey-40.1 {
  execsql {
    CREATE TABLE parent(x PRIMARY KEY, y);
    CREATE TABLE child1(a, 
      b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT

  8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
  9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE}
} {
  do_test e_fkey-40.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes
}

#-------------------------------------------------------------------------


# Test that "NO ACTION" means that nothing happens to a child row when
# it's parent row is updated or deleted.
#
# EVIDENCE-OF: R-19971-54976 Configuring "NO ACTION" means just that:
# when a parent key is modified or deleted from the database, no special
# action is taken.
#
drop_all_tables
do_test e_fkey-41.1 {
  execsql {
    CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2));
    CREATE TABLE child(c1, c2, 
      FOREIGN KEY(c1, c2) REFERENCES parent

  catchsql COMMIT
} {1 {foreign key constraint failed}}
do_test e_fkey-41.4 {
  execsql ROLLBACK
} {}

#-------------------------------------------------------------------------


# Test that "RESTRICT" means the application is prohibited from deleting
# or updating a parent table row when there exists one or more child keys
# mapped to it.
#
# EVIDENCE-OF: R-04272-38653 The "RESTRICT" action means that the
# application is prohibited from deleting (for ON DELETE RESTRICT) or
# modifying (for ON UPDATE RESTRICT) a parent key when there exists one
# or more child keys mapped to it.
#
drop_all_tables
do_test e_fkey-41.1 {
  execsql {
    CREATE TABLE parent(p1, p2);
    CREATE UNIQUE INDEX parent_i ON parent(p1, p2);
    CREATE TABLE child1(c1, c2, 

  catchsql { DELETE FROM parent WHERE p1 = 'a' }
} {1 {foreign key constraint failed}}
do_test e_fkey-41.4 {
  catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' }
} {1 {foreign key constraint failed}}

#-------------------------------------------------------------------------


# Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE
# constraints, in that it is enforced immediately, not at the end of the 
# statement.
#
# EVIDENCE-OF: R-37997-42187 The difference between the effect of a
# RESTRICT action and normal foreign key constraint enforcement is that
# the RESTRICT action processing happens as soon as the field is updated
# - not at the end of the current statement as it would with an
# immediate constraint, or at the end of the current transaction as it
# would with a deferred constraint.
#
drop_all_tables
do_test e_fkey-42.1 {
  execsql {
    CREATE TABLE parent(x PRIMARY KEY);
    CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT);
    CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION);

  execsql { 
    REPLACE INTO parent VALUES('key2');
    SELECT * FROM child2;
  }
} {key2}

#-------------------------------------------------------------------------
# Test that RESTRICT is enforced immediately, even for a DEFERRED constraint.
#
# EVIDENCE-OF: R-24179-60523 Even if the foreign key constraint it is
# attached to is deferred, configuring a RESTRICT action causes SQLite
# to return an error immediately if a parent key with dependent child
# keys is deleted or modified.
#
drop_all_tables
do_test e_fkey-43.1 {
  execsql {
    CREATE TABLE parent(x PRIMARY KEY);
    CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT
      DEFERRABLE INITIALLY DEFERRED

  execsql {
    UPDATE child2 SET c = NULL;
    COMMIT;
  }
} {}

#-------------------------------------------------------------------------
# Test SET NULL actions.
#
# EVIDENCE-OF: R-03353-05327 If the configured action is "SET NULL",
# then when a parent key is deleted (for ON DELETE SET NULL) or modified
# (for ON UPDATE SET NULL), the child key columns of all rows in the
# child table that mapped to the parent key are set to contain SQL NULL
# values.
#
drop_all_tables
do_test e_fkey-44.1 {
  execsql {
    CREATE TABLE pA(x PRIMARY KEY);
    CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL);
    CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL);

  }
} {X'8765'}
do_test e_fkey-44.5 {
  execsql { SELECT quote(c) FROM cB }
} {NULL}

#-------------------------------------------------------------------------
# Test SET DEFAULT actions.
#
# EVIDENCE-OF: R-43054-54832 The "SET DEFAULT" actions are similar to
# "SET NULL", except that each of the child key columns is set to
# contain the columns default value instead of NULL.
#
drop_all_tables
do_test e_fkey-45.1 {
  execsql {
    CREATE TABLE pA(x PRIMARY KEY);
    CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT);
    CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT);

  }
} {X'0000' X'9999' X'8765'}
do_test e_fkey-45.5 {
  execsql { SELECT quote(c) FROM cB }
} {X'9999'}

#-------------------------------------------------------------------------
# Test ON DELETE CASCADE actions.
#
# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or

# update operation on the parent key to each dependent child key.
#
# EVIDENCE-OF: R-61809-62207 For an "ON DELETE CASCADE" action, this
# means that each row in the child table that was associated with the
# deleted parent row is also deleted.
#
drop_all_tables
do_test e_fkey-46.1 {
  execsql {
    CREATE TABLE p1(a, b UNIQUE);
    CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d);
    INSERT INTO p1 VALUES(NULL, NULL);

} {{} {}}
do_test e_fkey-46.4 {
  execsql { SELECT * FROM p1 }
} {}


#-------------------------------------------------------------------------
# Test ON UPDATE CASCADE actions.
#
# EVIDENCE-OF: R-13877-64542 For an "ON UPDATE CASCADE" action, it means
# that the values stored in each dependent child key are modified to
# match the new parent key values.
#
# EVIDENCE-OF: R-61376-57267 A "CASCADE" action propagates the delete or
# update operation on the parent key to each dependent child key.
#
drop_all_tables
do_test e_fkey-47.1 {
  execsql {
    CREATE TABLE p1(a, b UNIQUE);
    CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d);
    INSERT INTO p1 VALUES(NULL, NULL);

  }
} {{} {} 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 {

} {2 {Frank Sinatra} 100 {Dean Martin}}
do_test e_fkey-48.4 {
  execsql { SELECT * FROM track }
} {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2}


#-------------------------------------------------------------------------


# Verify that adding an FK action does not absolve the user of the 
# requirement not to violate the foreign key constraint.
#
# EVIDENCE-OF: R-53968-51642 Configuring an ON UPDATE or ON DELETE
# action does not mean that the foreign key constraint does not need to
# be satisfied.
#
drop_all_tables
do_test e_fkey-49.1 {
  execsql {
    CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a));
    CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c',
      FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT

} {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"
# action is configured, but there is no row in the parent table that
# corresponds to the default values of the child key columns, deleting a
# parent key while dependent child keys exist still causes a foreign key
# violation.
#
drop_all_tables
do_test e_fkey-50.1 {
  execsql {
    CREATE TABLE artist(
      artistid    INTEGER PRIMARY KEY, 
      artistname  TEXT

  execsql { SELECT * FROM artist }
} {0 {Unknown Artist}}
do_test e_fkey-50.5 {
  execsql { SELECT * FROM track }
} {14 {Mr. Bojangles} 0}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-09564-22170
#
# Check that the order of steps in an UPDATE or DELETE on a parent 
# table is as follows:
#
#   1. Execute applicable BEFORE trigger programs,
#   2. Check local (non foreign key) constraints,
#   3. Update or delete the row in the parent table,

    UPDATE parent SET x = 22;
    SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
  }
} {22 23 21 xxx 23}


#-------------------------------------------------------------------------


# Verify that ON UPDATE actions only actually take place if the parent key
# is set to a new value that is distinct from the old value. The default
# collation sequence and affinity are used to determine if the new value
# is 'distinct' from the old or not.
#
# EVIDENCE-OF: R-27383-10246 An ON UPDATE action is only taken if the
# values of the parent key are modified so that the new parent key
# values are not equal to the old.
#
drop_all_tables
do_test e_fkey-52.1 {
  execsql {
    CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b));
    CREATE TABLE apollo(c, d, 
      FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE

  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

} {null}

###########################################################################
### SECTION 5: CREATE, ALTER and DROP TABLE commands
###########################################################################

#-------------------------------------------------------------------------
# Test that parent keys are not checked when tables are created.
#
# EVIDENCE-OF: R-36018-21755 The parent key definitions of foreign key

# constraints are not checked when a table is created.
#
# EVIDENCE-OF: R-25384-39337 There is nothing stopping the user from
# creating a foreign key definition that refers to a parent table that
# does not exist, or to parent key columns that do not exist or are not
# collectively bound by a PRIMARY KEY or UNIQUE constraint.
#
# Child keys are checked to ensure all component columns exist. If parent
# key columns are explicitly specified, SQLite checks to make sure there
# are the same number of columns in the child and parent keys. (TODO: This
# is tested but does not correspond to any testable statement.)
#


# Also test that the above statements are true regardless of whether or not
# foreign keys are enabled:  "A CREATE TABLE command operates the same whether
# or not foreign key constraints are enabled."
#
# EVIDENCE-OF: R-08908-23439 A CREATE TABLE command operates the same
# whether or not foreign key constraints are enabled.
# 
foreach {tn zCreateTbl lRes} {
  1 "CREATE TABLE t1(a, b REFERENCES t1)"                            {0 {}}
  2 "CREATE TABLE t1(a, b REFERENCES t2)"                            {0 {}}
  3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)"          {0 {}}
  4 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}
  5 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)"          {0 {}}

    drop_all_tables
    execsql {PRAGMA foreign_keys = ON}
    catchsql $zCreateTbl
  } $lRes
}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-47952-62498 It is not possible to use the "ALTER TABLE
# ... ADD COLUMN" syntax to add a column that includes a REFERENCES
# clause, unless the default value of the new column is NULL. Attempting
# to do so returns an error.
#
proc test_efkey_6 {tn zAlter isError} {
  drop_all_tables 

  do_test e_fkey-56.$tn.1 "
    execsql { CREATE TABLE tbl(a, b) }
    [list catchsql $zAlter]
  " [lindex {{0 {}} {1 {Cannot add a REFERENCES column with non-NULL default value}}} $isError]

}

test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0
test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0
test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1

#-------------------------------------------------------------------------


# Test that ALTER TABLE adjusts REFERENCES clauses when the parent table
# is RENAMED.
#
# EVIDENCE-OF: R-47080-02069 If an "ALTER TABLE ... RENAME TO" command
# is used to rename a table that is the parent table of one or more
# foreign key constraints, the definitions of the foreign key
# constraints are modified to refer to the parent table by its new name
#
# Test that these adjustments are visible in the sqlite_master table.
#
# EVIDENCE-OF: R-63827-54774 The text of the child CREATE TABLE
# statement or statements stored in the sqlite_master table are modified
# to reflect the new parent table name.
#
do_test e_fkey-56.1 {
  drop_all_tables
  execsql {
    CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b));

    CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE);

  {CREATE TABLE "p"(a REFERENCES "p", b, PRIMARY KEY(b))}                   \
  {CREATE TABLE c1(c, d REFERENCES "p" ON UPDATE CASCADE)}                  \
  {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)}  \
  {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \
]

#-------------------------------------------------------------------------



# Check that a DROP TABLE does an implicit DELETE FROM. Which does not
# cause any triggers to fire, but does fire foreign key actions.
#
# EVIDENCE-OF: R-14208-23986 If foreign key constraints are enabled when
# it is prepared, the DROP TABLE command performs an implicit DELETE to
# remove all rows from the table before dropping it.
#
# EVIDENCE-OF: R-11078-03945 The implicit DELETE does not cause any SQL
# triggers to fire, but may invoke foreign key actions or constraint
# violations.
#
do_test e_fkey-57.1 {
  drop_all_tables
  execsql {
    CREATE TABLE p(a, b, PRIMARY KEY(a, b));

    CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET NULL);

      DELETE FROM p;
      SELECT * FROM log;
    ROLLBACK;
  }
} {{delete 1}}

#-------------------------------------------------------------------------


# If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the
# DROP TABLE command fails.
#
# EVIDENCE-OF: R-32768-47925 If an immediate foreign key constraint is
# violated, the DROP TABLE statement fails and the table is not dropped.
#
do_test e_fkey-58.1 {
  execsql { 
    DELETE FROM c1;
    DELETE FROM c2;
    DELETE FROM c3;
  }
  execsql { INSERT INTO c5 VALUES('a', 'b') }

    SELECT * FROM p;
    SELECT * FROM c5;
    ROLLBACK;
  }
} {a b a b}

#-------------------------------------------------------------------------


# If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting
# to commit the transaction fails unless the violation is fixed.
#
# EVIDENCE-OF: R-05903-08460 If a deferred foreign key constraint is
# violated, then an error is reported when the user attempts to commit
# the transaction if the foreign key constraint violations still exist
# at that point.
#
do_test e_fkey-59.1 {
  execsql { 
    DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ;
    DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ;
    DELETE FROM c7 
  }

} {1 {foreign key constraint failed}}
do_test e_fkey-59.5 {
  execsql { INSERT INTO p VALUES('a', 'b') }
  execsql COMMIT
} {}

#-------------------------------------------------------------------------


# Any "foreign key mismatch" errors encountered while running an implicit
# "DELETE FROM tbl" are ignored.
#
# EVIDENCE-OF: R-57242-37005 Any "foreign key mismatch" errors
# encountered as part of an implicit DELETE are ignored.
#
drop_all_tables
do_test e_fkey-60.1 {
  execsql {
    PRAGMA foreign_keys = OFF;

    CREATE TABLE p(a PRIMARY KEY, b REFERENCES nosuchtable);

} {1 {foreign key mismatch}}
do_test e_fkey-60.6 {
  execsql { DROP TABLE c2 }
  execsql { DELETE FROM p }
} {}

#-------------------------------------------------------------------------


# Test that the special behaviours of ALTER and DROP TABLE are only
# activated when foreign keys are enabled. Special behaviours are:
#
#   1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL 
#      default value.
#   2. Modifying foreign key definitions when a parent table is RENAMEd.
#   3. Running an implicit DELETE FROM command as part of DROP TABLE.
#
# EVIDENCE-OF: R-54142-41346 The properties of the DROP TABLE and ALTER
# TABLE commands described above only apply if foreign keys are enabled.
#
do_test e_fkey-61.1.1 {
  drop_all_tables
  execsql { CREATE TABLE t1(a, b) }
  catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 }
} {1 {Cannot add a REFERENCES column with non-NULL default value}}
do_test e_fkey-61.1.2 {
  execsql { PRAGMA foreign_keys = OFF }

} {}

###########################################################################
### SECTION 6: Limits and Unsupported Features
###########################################################################

#-------------------------------------------------------------------------



# Test that MATCH clauses are parsed, but SQLite treats every foreign key
# constraint as if it were "MATCH SIMPLE".
#
# EVIDENCE-OF: R-24728-13230 SQLite parses MATCH clauses (i.e. does not
# report a syntax error if you specify one), but does not enforce them.
#
# EVIDENCE-OF: R-24450-46174 All foreign key constraints in SQLite are
# handled as if MATCH SIMPLE were specified.
#
foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] {
  drop_all_tables
  do_test e_fkey-62.$zMatch.1 {
    execsql "
      CREATE TABLE p(a, b, c, PRIMARY KEY(b, c));
      CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch);

    # Check that the FK is enforced properly if there are no NULL values 
    # in the child key columns.
    catchsql { INSERT INTO c VALUES('a', 2, 4) }
  } {1 {foreign key constraint failed}}
}

#-------------------------------------------------------------------------


# Test that SQLite does not support the SET CONSTRAINT statement. And
# that it is possible to create both immediate and deferred constraints.
#
# EVIDENCE-OF: R-21599-16038 In SQLite, a foreign key constraint is
# permanently marked as deferred or immediate when it is created.
#
drop_all_tables
do_test e_fkey-62.1 {
  catchsql { SET CONSTRAINTS ALL IMMEDIATE }
} {1 {near "SET": syntax error}}
do_test e_fkey-62.2 {
  catchsql { SET CONSTRAINTS ALL DEFERRED }

  execsql { 
    DELETE FROM cd;
    COMMIT;
  }
} {}

#-------------------------------------------------------------------------


# Test that the maximum recursion depth of foreign key action programs is
# governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH
# settings.
#
# EVIDENCE-OF: R-42264-30503 The SQLITE_MAX_TRIGGER_DEPTH and
# SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable
# depth of trigger program recursion. For the purposes of these limits,
# foreign key actions are considered trigger programs.
#
proc test_on_delete_recursion {limit} {
  drop_all_tables
  execsql { 
    BEGIN;
    CREATE TABLE t0(a PRIMARY KEY, b);
    INSERT INTO t0 VALUES('x0', NULL);

  test_on_update_recursion 6
} {1 {too many levels of trigger recursion}}
do_test e_fkey-63.2.5 {
  sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
} {5}

#-------------------------------------------------------------------------


# The setting of the recursive_triggers pragma does not affect foreign
# key actions.
#
# EVIDENCE-OF: R-51769-32730 The PRAGMA recursive_triggers setting does
# not not affect the operation of foreign key actions.
#
foreach recursive_triggers_setting [list 0 1 ON OFF] {
  drop_all_tables
  execsql "PRAGMA recursive_triggers = $recursive_triggers_setting"

  do_test e_fkey-64.$recursive_triggers_setting.1 {
    execsql {