SQLite Forum

I can't get the foreign_keys pragma to "stick"
Login

I can't get the foreign_keys pragma to "stick"

(1) By omortis on 2020-10-29 19:45:34 [source]

Hello,

As part of a unit test apparatus, I have created an SQL file that loads into sqlite3 and creates a bunch of tables for me. In that sql file, I attempt to enforce foreign_keys with the pragma:

PRAGMA foreign_keys = on; -- also 1, true

The pragma seems to work when I load the sql file directly:

$ sqlite3 -init sqlite3-empty.sql
-- Loading resources from sqlite3-empty.sql
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> pragma foreign_keys;
1
sqlite> .quit

But the pragma is not set correctly in the resulting DB file. This is loaded in a golang program (more detail here but the same effect can be seen at the CLI:

$ sqlite3 -init sqlite3-empty.sql
-- Loading resources from sqlite3-empty.sql
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> pragma foreign_keys;
1
sqlite> .backup backup.db
sqlite> .quit
$ sqlite3 backup.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> pragma foreign_keys;
foreign_keys
------------
0
sqlite>

I can get it to "stick" if I set the pragma in my ~/.sqliterc file but this is a non-starter as this rig has to work in automated testing.

Can I set the pragma via an environment variable or something? Any suggestions?

Thanks in advance!

(2) By Ryan Smith (cuz) on 2020-10-29 21:11:44 in reply to 1 [link] [source]

But the pragma is not set correctly in the resulting DB file

The Foreign-Key checking is a state of the Connection, not the DB file, which is usually not a problem since most SQLite users open their own DBs within their own code.

This is loaded in a golang program

There is unfortunately no way to inform or instruct any other SQLite DB program, DB-Admin manager, CLI, etc. that it should open the file itself with foreign keys turned on. Every program decides that for itself. If you are coding the golang program, it should not be a problem.

Something like the CLI will eat your script and if in there the statement:

PRAGMA foreign_keys = on;
appears (before any transaction), then it should be honoured - but there is no way at all to enforce it via the DB file itself.

Best you can do is see which ones do honour it, and use one of them, or write your own small CLI (for whatever the intent of the golang program is) that loads the file and executes the sql in it, in the way you like it to be done.

You could also download the official SQLite CLI from the site and tweak the code how you need it.

Probably not what you were hoping to hear, but Good luck! :)

(3) By David Raymond (dvdraymond) on 2020-10-30 11:54:50 in reply to 1 [link] [source]

There's a compile time option to set the default to on. But as Ryan said, every connection is its own thing, and you can't force it upon someone else's connection.