Updating foreign key
Indices are not required for child key columns but they are almost always beneficial.
Returning to the example in section 1, each time an application deletes a row from the artist table (the parent table), it performs the equivalent of the following SELECT statement to search for referencing rows in the track table (the child table). in the above is replaced with the value of the artistid column of the record being deleted from the artist table (recall that the trackartist column is the child key and the artistid column is the parent key).
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.
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.
The foreign key constraints created as part of tables child1, child2 and child3 are all fine.
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.
Attempting to do so does not return an error; it simply has no effect.
Usually, the parent key of a foreign key constraint is the primary key of the parent table.
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.
If these queries cannot use an index, they are forced to do a linear scan of the entire child table.
In a non-trivial database, this may be prohibitively expensive.
The following command-line session demonstrates this: 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).
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).
The PRAGMA foreign_keys command is a no-op in this configuration.