How do I activate foreign keys in C, using sqlite?

Question

I need to use foreign keys at my program, but I don´t know how to activate this PRAGMA using C Language. (I wanna use CASCADE modes.)

With sqlite binary, I just write PRAGMA foreign_keys = on; and it works.

My question is: How I can do this in C?

Answer 1

In part, the SQLLite manual says:

In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined. If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced. The PRAGMA foreign_keys command is a no-op in this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error).

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.) The application can can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled.

And the page about pragmas says:

• Specific pragma statements may be removed and others added in future releases of SQLite. There is no guarantee of backwards compatibility.
• No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact.
• Some pragmas take effect during the SQL compilation stage, not the execution stage. This means if using the C-language sqlite3_prepare(), sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper interface), the pragma may run during the sqlite3_prepare() call, not during the sqlite3_step() call as normal SQL statements do. Or the pragma might run during sqlite3_step() just like normal SQL statements. Whether or not the pragma runs during sqlite3_prepare() or sqlite3_step() depends on the pragma and on the specific release of SQLite.

So, you prepare the PRAGMA statement - and execute it to make sure it takes effect.

There might be a more specialized API; you can read the manual too.