All geek questions in one place

How to activate foreign keys in C using sqlite?

I need to use foreign keys in my program, but I do not know how to activate this PRAGMA using the C language. (I want to use CASCADE modes.)

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

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

+4
source share
2 answers

The SQLLite manual part says:

To use foreign key constraints in SQLite, the library must be compiled without the specific SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER. If SQLITE_OMIT_TRIGGER is defined, but there is no SQLITE_OMIT_FOREIGN_KEY, then SQLite behaves the same as before version 3.6.19 - foreign key definitions are parsed and can be queried using PRAGMA foreign_key_list, but foreign key restrictions are not applied. The PRAGMA foreign_keys command does not work in this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be analyzed (attempting to specify a foreign key definition is a syntax error).

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

sqlite> PRAGMA foreign_keys = ON;

Foreign key restrictions are disabled by default (for backward compatibility), so they must be activated separately for each connection to the database separately. (Note, however, that future releases of SQLite may change so that foreign keys are restricted by default. Careful developers will not make any assumptions about whether foreign keys are enabled by default, but will instead enable or disable them as needed. ) An application can also use the PRAGMA foreign_keys statement to determine if foreign keys are enabled.

And the page about pragmas says:

  • Specific pragma statements may be removed, while others will be added in future releases of SQLite. There is no guarantee of backward compatibility.
  • Error messages are not generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means that there is a typo in the pragma instruction, the library does not inform the user about it.
  • Some pragmas take effect at the SQL compilation stage, and not at runtime. This means that when using the C-language sqlite3_prepare (), sqlite3_step (), sqlite3_finalize () API (or similar in the wrapper interface), the pragma can be executed during the call to sqlite3_prepare (), and not during the call to sqlite3_step (), as usual SQL statements do. Or the pragma can work during sqlite3_step (), like regular SQL statements. Regardless of whether the pragma works during sqlite3_prepare () or sqlite3_step (), it depends on the pragma and the specific version of SQLite.

So, you are preparing the PRAGMA instruction and executing it to make sure that it takes effect.

There may be a more specialized API; You can also read the manual.

+5
source

C is exactly the same - just execute the statement "PRAGMA foreign_keys = ON" .

+2
source

Source: https://habr.com/ru/post/1334507/

More articles:


All Articles