Just getting started with SQLite and trying to figure out how to enable foreign key support.  I downloaded sqlite-tools-win32-x86-3430200.zip into Windows 10.  Here is a small session in sqlite3.exe:

SQLite version 3.43.2 2023-10-10 12:14:04
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA foreign_keys=1;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> PRAGMA foreign_keys=ON;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite>

I tried both 1 and ON as the argument for PRAGMA foreign_keys but it shows off in the dump.  What is needed to allow foreign key support, or am I misunderstanding this?

Foreign key enforcement is only at the connection level. It's not a property of the database itself.

The .dump command of the CLI is meant to dump out SQL commands to recreate the database. So it will always include that line to turn off foreign key enforcement, because if you have a dump from a database where the foreign keys were never enforced, and you're trying to load it in via a connection that currently does have foreign keys enforced, then it won't successfully load the data like you want it to.

To check if it has indeed taken hold, you can run the pragma without an assignment and check the results.


sqlite> pragma foreign_keys = off;

sqlite> pragma foreign_keys;
foreign_keys
0

sqlite> pragma foreign_keys = on;

sqlite> pragma foreign_keys;
foreign_keys
1

sqlite>