SQlite Foreign Key Support

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!

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

2014-02-11 13:58:34   Target by Host Settings database error code[19] - foreign key constraint failed

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 ?

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


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;
So it’s stored in the database and it should never be put back to OFF.

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.

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…)