In sqlite3, is there a foreign_key integrity check?

Question

I'm just teaching myself sqlite and so I was surprised to see my foreign_key constraint not working when I was able to delete a parent entry. I then learned after reading more that the pragma for foreign_key is off by default for each session. Seems odd there isn't a resource file (something like .exrc for vi for example) that you can use to setup pragmas by default for each session, but fine. So I have to recompile sqlite3 or just set it every time.

Anyway, my question is, after I deleted the parent, is there a way to do a post integrity check on foreign key constraints? I.e. just tell sqlite to run the same logic it runs when it does it originally if you had the pragma turned on at the time of the insert or delete etc?

I see a pragma integrity_check but that's just looking for corruption it seems.

Thanks, Justin

Answer 1

Beginning with SQLite 3.7.16, there is PRAGMA foreign_key_check.

Answer 2

Run the following command:

PRAGMA foreign_keys;

Result will be:

0 // foreign keys Disabled
1 // foreign keys Enabled

To enable or disable foreign keys run:

PRAGMA foreign_keys = ON;

or

PRAGMA foreign_keys = OFF;

More information Here