Foreign Keys, or more properly, Foreign Key Constraints are used to enforce parent-child relationships between tables. Foreign keys ensure there is referential integrity in the row data and that there aren’t orphaned child records. Parent-child relationships can be supported without foreign keys and enforced programmatically in the application layer, but database enforced relationship constraints provide an extra level of protection. It is not uncommon to run SQL directly against a database, and if business rules can be enforced in this scenario, your application data is better protected from inconsistency. SQLite provides excellent support for foreign keys but has some inherited limitations.
A Parent-Child relationship connects 2 tables, the parent table must have a primary key and the child table defines the foreign key. The child table records the parent table’s primary key and is known as a reference to the parent table. “The vast majority of foreign keys will reference a primary key, but if a column other than the primary key is used, that column must have a UNIQUE constraint, or it must have a single-column UNIQUE index.” (Kreibich)
SQLite provides excellent support for foreign keys and has some limitations. Some important points when creating foreign keys in SQLite are:
The SQL command examples used in this article are based on the following database.
You can see there are 2 parent tables and one child table. The child table has a foreign key constraint on each of the parent tables. The Foreign Keys enforce the rule that an order must be associated with an existing customer and an existing product.
Use the following statement to enable foreign key enforcement
PRAGMA foreign_keys = ON;Foreign keys are not enforced by default in order to avoid causing problems with databases created prior to foreign key support. “A future version of SQLite may have foreign key constraints enabled by default. If your application is dependent on this setting, it should explicitly turn it on or off.” (Kreibich)
There are two options here. The foreign key can be declared with the column definition by adding a references statement or by using a foreign key declaration after all column definitions.
CREATE TABLE IF NOT EXISTS orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
product_id INTEGER NOT NULL REFERENCES products (product_id),
quantity INTEGER NO NULL
);
CREATE TABLE IF NOT EXISTS orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL ,
product_id INTEGER NOT NULL,
quantity INTEGER NO NULL,
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
FOREIGN KEY (product_id)
REFERENCES products (product_id)
);
Applying foreign keys in this manner allows for flexibility allowing for declaration of a foreign key on a multi-column primary key.
The foreign keys in my example are not nullable, but this does not have to be the case. A foreign key could have a hull value if it is not required to be associated to a parent record. In this case the parent record is optional.
The default action for a foreign key constraint violation is to prevent the action causing the violation. There are other actions that can be taken however.
An update to a parent record should not change the primary key. The ON DELETE actions are therefore more interesting practically speaking than the ON UPDATE actions.
There are 5 options for actions to take when a parent record is deleted. They are:
“NO ACTION” is the default, and means that no action is taken. The parent row is not deleted as it violates the foreign key constraint.
“RESTRICT” disallows the delete The difference between RESTRICT and the foreign key constraint enforcement is the RESTRICT blocks the operation as soon as the field is updated, not at the conclusion of the statement execution.
“SET NULL” is useful in a scenario where the foreign key constraint is optional. In this scenario if the parent record is removed, the child record’s foreign keys mapping to that parent are set to null. If these fields are set to NOT NULL, an error occurs.
FOREIGN KEY (product_id)
REFERENCES products (product_id)
ON DELETE SET NULL“SET DEFAULT” allows you to set the foreign key value to a default when the parent is deleted. This is handy for scenarios where a NULL value isn’t valid and defaults can be used to simplify data configuration. The default value set on the foreign key column is applied.
“CASCADE” is the most common action and will cascade the delete operation to all child records associated with the parent key.
FOREIGN KEY (product_id)
REFERENCES products (product_id)
ON DELETE CASCADEForeign Keys are set to "Immediate" by default, meaning that any statement that violates the constraint will trigger an error as soon as it is completed execution.
Deferred Foreign Keys will not trigger an error until a COMMIT is executed. The database may be in an inconsistent state while a transaction is in progress,
This is an example of a DEFERRABLE foreign key:
FOREIGN KEY (product_id)
REFERENCES products (product_id)
DEFERRABLEForeign keys are not indexed by default the way that primary keys are. The SQLite documentation on foreign keys suggests that in most cases you will want to index your foreign keys.
CREATE INDEX productsindex ON products(product_id);The SQLite documentation explains that deleting rows in parent table will cause a SELECT against the child tables which does a lookup on the foreign key column, so indexing this column decreases the lookup time.
I am using Windows 7 with SQLite3 version 3.39.4.
The database scripts and commands to create the Customer Orders database are as follows.
I started by creating a folder in my Projects folder off the root called ‘SQLiteFKDB’. To create a database in that folder I change directory to it and I ran the following command:
Sqlite3 SQLiteFKDBThis creates a database file in the current folder and opens the sqlite command line.
Create the following script file to create the initial database. I’ve named my file “CreateDB-1.sql”.
CREATE TABLE IF NOT EXISTS customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS products (
product_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
description TEXT NOT NULL,
price REAL
);
CREATE TABLE IF NOT EXISTS orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL ,
product_id INTEGER NOT NULL,
quantity INTEGER NO NULL,
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
FOREIGN KEY (product_id)
REFERENCES products (product_id)
);
Create the database schema with the following command
Sqlite3 SQLiteFKDB “.read CreateDB-1.sql”https://www.sqlitetutorial.net/sqlite-foreign-key/
“Using SQLite”, Jay A, Kreibich, Oreilly and Associates, 2010
“The Definitive Guide to SQLite” Second Edition, Grant Allen and Mike Owens, Apress, 2010
https://www.sqlite.org/foreignkeys.html