SQlite Foreign Key Support

Databases
1

Hello all. I am designing a set of tables within a SQLite database and would like to use Foreign Keys as a constraint with Xojo 2013r4.1 SQLite (3.7.17). Will I need to set the sqlite statement prior to Schema setup?

PRAGMA foreign_keys = ON;

Thanks again!

2

Yes this works perfectly after you enable it for each Database you are wanting to enable it for.

SQLExecute("PRAGMA FOREIGN_KEYS = ON;") 
2014-02-11 13:58:34   Target by Host Settings database error code[19] - foreign key constraint failed
3

one question: do I have to call this pragma each time I open the sqlite database,
or is it stored in the database until I call it to OFF ?
thanks.

4

You have to call the pragma “for each database connection” in order to enable FK support.

https://www.sqlite.org/foreignkeys.html

5

I put the command
PRAGMA foreign_keys = ON;
on the first line of the script creating my database and it’s there for good
(Script written with TextWrangler and install with .read command in the Terminal)
I close the database;
reopen it and querying the status sends me 1
PRAGMA foreign_keys;
1
So it’s stored in the database and it should never be put back to OFF.
jjc_Mtl

6

Interesting. The official SQLite FK docs still say it needs to be enabled for each connection.

I don’t see any mention of a change in this behavior in the release notes, either.

7

Oups!
update:
on a database I use regularly, I just test the PRAGMA foreign_keys; and it sent me 0 !!!
I always create my databases with foreign_keys ON
I tested the referential integrity by inserting data and foreign_keys is really OFF
SQLite allows me to INSERT any kind of stupidity…
So, just follow the rule (the SQLite one, not mine…)
sorry.
jjc_Mtl