Does SQLite support foreign keys?

Short Answer:
FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers.

A. Foreign Key (FK)
FK is a referential constraint between two tables. A FK is a field(s) that reference to the primary key of another table. The purpose of the FK is to ensure referential integrity of the data. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL).

This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.

FK is defined in DDL (Data Definition Language), so we do not code in our application. Once we define FK constraints, they must remain true while inputting/modifying/deleting data in the database. Constraints are used to enforce table rules and prevent data dependent deletion (enforce database integrity).


B. Enforce Foreign Key Constraint using PRAGMA
The PRAGMA statement is a SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data. The foreign_keys pragma controls the enforcement of foreign key constraints in all of the attached databases. If set to off, foreign key constraints are ignored. The default value is off.

For many years SQLite would parse foreign key constraints, but was unable to enforce them. When native support for foreign key constraints was finally added to the SQLite core, enforcement was left off by default to avoid any backwards compatibility issues.

This default value may change in future releases. To avoid problems, it is recommended that an application explicitly set the pragma one way or the other. This pragma cannot be set when there is an active transaction in progress.

1. Parent table: We define Primary Key
CREATE TABLE parentorder (
jmid VARCHAR(16) NOT NULL PRIMARY KEY,
jmdate DATE NOT NULL,
jmsupp VARCHAR(6) NOT NULL);

2. Child table: We define a reference to primary key above
CREATE TABLE childorder (
jm2id VARCHAR(16) NOT NULL CONSTRAINT fk_childorder_id REFERENCES parentorder(jmid),
jm2no VARCHAR(3) NOT NULL,
jm2itemid VARCHAR(6) NOT NULL,
jm2qty INTEGER NOT NULL,
PRIMARY KEY (jm2id,jm2no));

Test them in SQLite
sqlite>select sqlite_version();
3.6.23.1
sqlite> insert into parentorder values ('A1','2010-05-08','SUP1');
sqlite> insert into childorder values ('A1','P','CODE1', 10.5);
sqlite> insert into childorder values ('A1','V','CODE2', 11);

here we try with unexisting key in the primary key of parentorder table.
sqlite> insert into childorder values ('A2','X','CODE3', 12);

We see, FK constraint does not work, because the data A2 successfully inserted! It should raise an error for A2 is not exist in parent table.

Let's continue...with the PRAGMA statement
sqlite> PRAGMA foreign_keys;
0

Here we enforce the foreign key constraint to ON by using PRAGMA statement
sqlite> PRAGMA foreign_keys=1;
sqlite> update childorder set jm2id='A3' WHERE jm2id='A2';
Error: foreign key constraint failed

Look! We could not change A2 to A3, it means the FK constraint works!

sqlite> update childorder set jm2id='A1' WHERE jm2id='A2';
sqlite> select * from childorder;
A1 P CODE1 10.5
A1 V CODE2 11
A1 X CODE3 12

sqlite> select * from parentorder;
A1 2010-05-08 SUP1

sqlite> update childorder set jm2id='A3' WHERE jm2id='A1';
Error: foreign key constraint failed

C. Using Pragma in Program
using C#:
conn = new SQLiteConnection(connString);
conn.Open();
const string sqlString = "PRAGMA foreign_keys = ON;";
SQLiteCommand command = new SQLiteCommand(sqlString, conn); command.ExecuteNonQuery();

D. Trigger
Maintain FK constraints in above scenario might be not simple as it should be, such as enforce FK constraint each time we use database or when using application through connection string, or set the pragma in SQLite command line. For these reason, below we implemet triggers for FK constraints. See more.

-- Foreign Key Preventing insert
CREATE TRIGGER fki_childorder_jm2id_parentorder_jmid
BEFORE INSERT ON [childorder]
FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "childorder" violates foreign key constraint "fki_childorder_jm2id_parentorder_jmid"')
WHERE (SELECT jmid FROM parentorder WHERE jmid = NEW.jm2id) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_childorder_jm2id_parentorder_jmid
BEFORE UPDATE ON [childorder] FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'update on table "childorder" violates foreign key constraint "fku_childorder_jm2id_parentorder_jmid"')
WHERE
(SELECT jmid FROM parentorder WHERE jmid = NEW.jm2id) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_childorder_jm2id_parentorder_jmid
BEFORE DELETE ON parentorder
FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'delete on table "parentorder" violates foreign key constraint "fkd_childorder_jm2id_parentorder_jmid"')
WHERE
(SELECT jm2id FROM childorder WHERE jm2id = OLD.jmid) IS NOT NULL;
END;

E. Testing Trigger Code
sqllite>.exit
C:\sqllite3>sqlite tesdb
SQLite version 3.6.23.1

sqlite> copy-paste fki_childorder_jm2id_parentorder_jmid trigger above!
sqlite> insert into childorder values ('A1ERR','P','CODE1', 10.5);
Error: insert on table "childorder" violates foreign key constraint "fki_childorder_jm2id_parentorder_jmid"

sqlite> do the same for fku_childorder_jm2id_parentorder_jmid!
sqlite> update childorder set jm2id='TESTING';
Error: update on table "childorder" violates foreign key constraint "fku_childorder_jm2id_parentorder_jmid"

sqlite> do the same for fkd_childorder_jm2id_parentorder_jmid!
sqlite> delete from parentorder;Error: delete on table "parentorder" violates foreign key constraint "fkd_childorder_jm2id_parentorder_jmid"

--This pragma returns one row for each foreign key that references a column in the argument table.
sqlite> pragma foreign_key_list('childorder');
0 0 parentorder jm2id jmid NO ACTION NO ACTION NONE


F. Utility
1. Use this utility to generate triggers to enforce foreign key constraints with sqlite, operates on database schema and understands most CREATE TABLE syntax. Win32 and linux x86 binaries included, public domain source code.

2. The triggers code above is generated using this web tool. You could try by copy-paste the two Create Table statement above (B.1 and B.2) then click Submit.


G. Trigger vs Constraint
Constraint is a rule define in DDL statement (Foreign Key, Primary Key, etc).
Trigger is a rule define using DML statement.
Both are useful in special situation.

Using triggers we could define complex processing logic, and also support all of the functionality of constraints; however, DML triggers are not always the best method for a given feature.

Entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints or are created independently of constraints. Domain integrity should be enforced through CHECK constraints, and referential integrity (RI) should be enforced through FOREIGN KEY constraints, assuming their features meet the functional needs of the application. DML triggers are most useful when the features supported by constraints cannot meet the functional needs of the application.

Each RDBMS has their way for implementation of constraints and triggers, and we also know that there is SQL standards to meet. For constraints and triggers,  I keep these guidance:
-FK constraints can validate a column value only with an exact match to a value in another column, unless the REFERENCES clause defines a cascading referential action.
-Constraints can communicate about errors only through standardized system error messages.
-DML triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently through cascading referential integrity constraints.
-DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification. Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. However, FOREIGN KEY constraints are usually used for this purpose.
-If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

Wish that in the next officials release of SQLite, the foreign keys constraints come with default on.