SQLite

proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db }

###########################################################################
### SECTION 2: Enabling Foreign Key Support
###########################################################################

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-33710-56344 In order to use foreign key constraints in
# SQLite, the library must be compiled with neither
# SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined.

#
ifcapable trigger&&foreignkey {
  do_test e_fkey-1 {
    execsql {
      PRAGMA foreign_keys = ON;
      CREATE TABLE p(i PRIMARY KEY);
      CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
      INSERT INTO p VALUES('hello');
      INSERT INTO c VALUES('hello');
      UPDATE p SET i = 'world';
      SELECT * FROM c;
    }
  } {world}
}

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


# Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY.
#
# EVIDENCE-OF: R-44697-61543 If SQLITE_OMIT_TRIGGER is defined but
# SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to
# version 3.6.19 - foreign key definitions are parsed and may be queried
# using PRAGMA foreign_key_list, but foreign key constraints are not
# enforced.
#
# 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.
#
# EVIDENCE-OF: R-22567-44039 The PRAGMA foreign_keys command is a no-op
# in this configuration.
#
# EVIDENCE-OF: R-41784-13339 Tip: If the command "PRAGMA foreign_keys"
# returns no data instead of a single row containing "0" or "1", then
# the version of SQLite you are using does not support foreign keys
# (either because it is older than 3.6.19 or because it was compiled
# with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
#
reset_db
ifcapable !trigger&&foreignkey {
  do_test e_fkey-2.1 {
    execsql {
      PRAGMA foreign_keys = ON;
      CREATE TABLE p(i PRIMARY KEY);

  do_test e_fkey-2.3 {
    execsql { PRAGMA foreign_keys }
  } {}
}


#-------------------------------------------------------------------------
# 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).
#
# /* EV: R-58428-36660 */
#
# Specifically, test that foreign key constraints cannot even be parsed 
# in such a build.
#
reset_db

}

ifcapable !foreignkey||!trigger { finish_test ; return }
reset_db


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with

# foreign key constraints enabled, it must still be enabled by the
# application at runtime, using the PRAGMA foreign_keys command.
#

# This also tests that foreign key constraints are disabled by default.
#
# EVIDENCE-OF: R-59578-04990 Foreign key constraints are disabled by
# default (for backwards compatibility), so must be enabled separately
# for each database connection separately.
#
drop_all_tables
do_test e_fkey-4.1 {
  execsql {
    CREATE TABLE p(i PRIMARY KEY);
    CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
    INSERT INTO p VALUES('hello');

    INSERT INTO c VALUES('hello');
    UPDATE p SET i = 'world';
    SELECT * FROM c;
  } 
} {world}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-15278-54456 The application can can also use a PRAGMA
# foreign_keys statement to determine if foreign keys are currently
# enabled.
#


# This also tests the example code in section 2 of foreignkeys.in.
#
# EVIDENCE-OF: R-11255-19907
# 
reset_db
do_test e_fkey-5.1 {
  execsql { PRAGMA foreign_keys }
} {0}
do_test e_fkey-5.2 {
  execsql { 
    PRAGMA foreign_keys = ON;
    PRAGMA foreign_keys;
  }
} {1}
do_test e_fkey-5.3 {
  execsql { 
    PRAGMA foreign_keys = OFF;
    PRAGMA foreign_keys;
  }
} {0}

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


# Test that it is not possible to enable or disable foreign key support
# while not in auto-commit mode.
#
# EVIDENCE-OF: R-46649-58537 It is not possible to enable or disable
# foreign key constraints in the middle of a multi-statement transaction
# (when SQLite is not in autocommit mode). Attempting to do so does not
# return an error; it simply has no effect.
#
reset_db
do_test e_fkey-6.1 {
  execsql {
    PRAGMA foreign_keys = ON;
    CREATE TABLE t1(a UNIQUE, b);
    CREATE TABLE t2(c, d REFERENCES t1(a));

###########################################################################
### SECTION 1: Introduction to Foreign Key Constraints
###########################################################################
execsql "PRAGMA foreign_keys = ON"

#-------------------------------------------------------------------------
# Verify that the syntax in the first example in section 1 is valid.
#
# EVIDENCE-OF: R-04042-24825 To do so, a foreign key definition may be
# added by modifying the declaration of the track table to the
# following: CREATE TABLE track( trackid INTEGER, trackname TEXT,
# trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES
# artist(artistid) );
#
do_test e_fkey-7.1 {
  execsql {
    CREATE TABLE artist(
      artistid    INTEGER PRIMARY KEY, 
      artistname  TEXT
    );
    CREATE TABLE track(
      trackid     INTEGER, 
      trackname   TEXT, 
      trackartist INTEGER,
      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
    );
  }
} {}

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


# EVIDENCE-OF: R-61362-32087 Attempting to insert a row into the track
# table that does not correspond to any row in the artist table will
# fail,
#
do_test e_fkey-8.1 {
  catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
} {1 {foreign key constraint failed}}
do_test e_fkey-8.2 {
  execsql { INSERT INTO artist VALUES(2, 'artist 1') }
  catchsql { INSERT INTO track VALUES(1, 'track 1', 1) }
} {1 {foreign key constraint failed}}
do_test e_fkey-8.2 {
  execsql { INSERT INTO track VALUES(1, 'track 1', 2) }
} {}

#-------------------------------------------------------------------------
# Attempting to delete a row from the 'artist' table while there are 
# dependent rows in the track table also fails.
#
# EVIDENCE-OF: R-24401-52400 as will attempting to delete a row from the
# artist table when there exist dependent rows in the track table
#
do_test e_fkey-9.1 {
  catchsql { DELETE FROM artist WHERE artistid = 2 }
} {1 {foreign key constraint failed}}
do_test e_fkey-9.2 {
  execsql { 
    DELETE FROM track WHERE trackartist = 2;
    DELETE FROM artist WHERE artistid = 2;
  }
} {}

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


# If the foreign key column (trackartist) in table 'track' is set to NULL,
# there is no requirement for a matching row in the 'artist' table.
#
# EVIDENCE-OF: R-23980-48859 There is one exception: if the foreign key
# column in the track table is NULL, then no corresponding entry in the
# artist table is required.
#
do_test e_fkey-10.1 {
  execsql {
    INSERT INTO track VALUES(1, 'track 1', NULL);
    INSERT INTO track VALUES(2, 'track 2', NULL);
  }
} {}

# /* EV: R-52486-21352 */
#
# 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:
# trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE
# artistid=trackartist)

# This procedure executes a test case to check that statement 
# R-52486-21352 is true after executing the SQL statement passed.
# as the second argument.
proc test_r52486_21352 {tn sql} {
  set res [catchsql $sql]
  set results {

#-------------------------------------------------------------------------
# /* EV: R-42412-59321 */
#
# 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.
#
drop_all_tables
do_test e_fkey-12.1 {
  execsql {
    CREATE TABLE artist(
      artistid    INTEGER PRIMARY KEY, 
      artistname  TEXT
    );
    CREATE TABLE track(
      trackid     INTEGER, 
      trackname   TEXT, 
      trackartist INTEGER NOT NULL,
      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
    );
  }
} {}
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';
  }

    DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');
    UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';
  }
} {}


#-------------------------------------------------------------------------
# EVIDENCE-OF: R-56032-24923 The foreign key constraint is satisfied if

# for each row in the child table either one or more of the child key
# columns are NULL, or there exists a row in the parent table for which
# each parent key column contains a value equal to the value in its
# associated child key column.
#


# Test also that the usual comparison rules are used when testing if there 
# is a matching row in the parent table of a foreign key constraint.
#
# EVIDENCE-OF: R-57765-12380 In the above paragraph, the term "equal"
# means equal when values are compared using the rules specified here.
#
drop_all_tables
do_test e_fkey-15.1 {
  execsql {
    CREATE TABLE par(p PRIMARY KEY);
    CREATE TABLE chi(c REFERENCES par);

    INSERT INTO par VALUES(1);

test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'"
test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'"
test_efkey_45 7 1 "INSERT INTO chi VALUES('1')"
test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')"
test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')"

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


# Specifically, test that when comparing child and parent key values the
# default collation sequence of the parent key column is used.
#
# EVIDENCE-OF: R-15796-47513 When comparing text values, the collating
# sequence associated with the parent key column is always used.
#
drop_all_tables
do_test e_fkey-16.1 {
  execsql {
    CREATE TABLE t1(a COLLATE nocase PRIMARY KEY);
    CREATE TABLE t2(b REFERENCES t1);
  }

  catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 }
} {1 {foreign key constraint failed}}
do_test e_fkey-16.4 {
  catchsql { DELETE FROM t1 WHERE rowid = 1 }
} {1 {foreign key constraint failed}}

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


# Specifically, test that when comparing child and parent key values the
# affinity of the parent key column is applied to the child key value
# before the comparison takes place.
#
# EVIDENCE-OF: R-04240-13860 When comparing values, if the parent key
# column has an affinity, then that affinity is applied to the child key
# value before the comparison is performed.
#
drop_all_tables
do_test e_fkey-17.1 {
  execsql {
    CREATE TABLE t1(a NUMERIC PRIMARY KEY);
    CREATE TABLE t2(b TEXT REFERENCES t1);
  }

} {1 {foreign key constraint failed}}

###########################################################################
### SECTION 3: Required and Suggested Database Indexes
###########################################################################

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


# A parent key must be either a PRIMARY KEY, subject to a UNIQUE 
# constraint, or have a UNIQUE index created on it.
#
# EVIDENCE-OF: R-13435-26311 Usually, the parent key of a foreign key
# constraint is the primary key of the parent table. If they are not the
# primary key, then the parent key columns must be collectively subject
# to a UNIQUE constraint or have a UNIQUE index.
# 
# Also test that if a parent key is not subject to a PRIMARY KEY or UNIQUE
# constraint, but does have a UNIQUE index created on it, then the UNIQUE index
# must use the default collation sequences associated with the parent key
# columns.
#
# EVIDENCE-OF: R-00376-39212 If the parent key columns have a UNIQUE
# index, then that index must use the collation sequences that are
# specified in the CREATE TABLE statement for the parent table.
#
drop_all_tables
do_test e_fkey-18.1 {
  execsql {
    CREATE TABLE t2(a REFERENCES t1(x));
  }
} {}

}


#-------------------------------------------------------------------------
# This block tests an example in foreignkeys.html. Several testable
# statements refer to this example, as follows
#
# EVIDENCE-OF: R-27484-01467
#
# FK Constraints on child1, child2 and child3 are Ok.
#


# Problem with FK on child4:
#
# EVIDENCE-OF: R-51039-44840 The foreign key declared as part of table
# child4 is an error because even though the parent key column is
# indexed, the index is not UNIQUE.
#
# Problem with FK on child5:
#
# EVIDENCE-OF: R-01060-48788 The foreign key for table child5 is an
# error because even though the parent key column has a unique index,
# the index uses a different collating sequence.
#
# Problem with FK on child6 and child7:
#
# EVIDENCE-OF: R-63088-37469 Tables child6 and child7 are incorrect
# because while both have UNIQUE indices on their parent keys, the keys
# are not an exact match to the columns of a single UNIQUE index.
#
drop_all_tables
do_test e_fkey-19.1 {
  execsql {
    CREATE TABLE parent(a PRIMARY KEY, b UNIQUE, c, d, e, f);
    CREATE UNIQUE INDEX i1 ON parent(c, d);
    CREATE INDEX i2 ON parent(e);

  catchsql { INSERT INTO child6 VALUES(2, 3) }
} {1 {foreign key mismatch}}
do_test e_fkey-19.5 {
  catchsql { INSERT INTO child7 VALUES(3) }
} {1 {foreign key mismatch}}

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





# Test errors in the database schema that are detected while preparing
# DML statements. The error text for these messages always matches 
# either "foreign key mismatch" or "no such table*" (using [string match]).
#
# EVIDENCE-OF: R-45488-08504 If the database schema contains foreign key
# errors that require looking at more than one table definition to
# identify, then those errors are not detected when the tables are
# created.
#
# EVIDENCE-OF: R-48391-38472 Instead, such errors prevent the
# application from preparing SQL statements that modify the content of
# the child or parent tables in ways that use the foreign keys.
#
# EVIDENCE-OF: R-03108-63659 The English language error message for
# foreign key DML errors is usually "foreign key mismatch" but can also
# be "no such table" if the parent table does not exist.
#
# EVIDENCE-OF: R-60781-26576 Foreign key DML errors are may be reported
# if: The parent table does not exist, or The parent key columns named
# in the foreign key constraint do not exist, or The parent key columns
# named in the foreign key constraint are not the primary key of the
# parent table and are not subject to a unique constraint using
# collating sequence specified in the CREATE TABLE, or The child table
# references the primary key of the parent without specifying the
# primary key columns and the number of primary key columns in the
# parent do not match the number of child key columns.
#
do_test e_fkey-20.1 {
  execsql {
    CREATE TABLE c1(c REFERENCES nosuchtable, d);

    CREATE TABLE p2(a, b, UNIQUE(a, b));
    CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p2(a, x));

    do_test e_fkey-20.$tn.6 {
      catchsql "INSERT INTO $ptbl SELECT ?, ?"
    } [list 1 $err]
  }
}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-19353-43643
#
# Test the example of foreign key mismatch errors caused by implicitly
# mapping a child key to the primary key of the parent table when the
# child key consists of a different number of columns to that primary key.
# 
drop_all_tables
do_test e_fkey-21.1 {

  catchsql { INSERT INTO child10 VALUES(1, 2, 3) }
} {1 {foreign key mismatch}}
do_test e_fkey-21.8 {
  catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) }
} {1 {foreign key mismatch}}

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


# Test errors that are reported when creating the child table. 
# Specifically:
#
#   * different number of child and parent key columns, and
#   * child columns that do not exist.
#
# EVIDENCE-OF: R-23682-59820 By contrast, if foreign key errors can be
# recognized simply by looking at the definition of the child table and
# without having to consult the parent table definition, then the CREATE
# TABLE statement for the child table fails.
#
# These errors are reported whether or not FK support is enabled.
#
# EVIDENCE-OF: R-33883-28833 Foreign key DDL errors are reported
# regardless of whether or not foreign key constraints are enabled when
# the table is created.
#
drop_all_tables
foreach fk [list OFF ON] {
  execsql "PRAGMA foreign_keys = $fk"
  set i 0
  foreach {sql error} {
    "CREATE TABLE child1(a, b, FOREIGN KEY(a, b) REFERENCES p(c))"

test_efkey_60 3 0 "INSERT INTO p1 VALUES(239, 231)"
test_efkey_60 4 0 "INSERT INTO c1 VALUES(239, 231)"
test_efkey_60 5 1 "INSERT INTO c2 VALUES(239, 231)"
test_efkey_60 6 0 "INSERT INTO p2 VALUES(239, 231)"
test_efkey_60 7 0 "INSERT INTO c2 VALUES(239, 231)"

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


# 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
#
# /* EV: R-15741-50893 */
#
# 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.
#
drop_all_tables

  test_efkey_61 $tn.2 0 "INSERT INTO parent VALUES(1, 2)"
  test_efkey_61 $tn.3 0 "INSERT INTO $c VALUES(1, 2)"

  execsql "DELETE FROM $c ; DELETE FROM parent"
}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-00279-52283
#
# Test an example showing that when a row is deleted from the parent 
# table, the child table is queried for orphaned rows as follows:
#
#   SELECT rowid FROM track WHERE trackartist = ?
#
# EVIDENCE-OF: R-23302-30956 If this SELECT returns any rows at all,
# then SQLite concludes that deleting the row from the parent table

# would violate the foreign key constraint and returns an error.
#
do_test e_fkey-25.1 {
  execsql {
    CREATE TABLE artist(
      artistid    INTEGER PRIMARY KEY, 
      artistname  TEXT
    );

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
#
# /* EV: R-61616-46700 */
#
# 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
#
# EVIDENCE-OF: R-61616-46700 Similar queries may be run if the content
# of the parent key is modified or a new row is inserted into the parent
# table.
#
#
drop_all_tables
do_test e_fkey-26.1 {
  execsql { CREATE TABLE parent(x, y, UNIQUE(y, x)) }
} {}
foreach {tn sql} {