SQLite Foreign Key Support
SQLite Foreign Key Support
SQLite
Does SQLite support foreign keys?
As of version 3.6.19 (2009-10-14), SQLite supports foreign key constraints. But enforcement of foreign key constraints is turned off by default (for backwards compatibility). To enable foreign key constraint enforcement, run PRAGMA foreign_keys=ON or compile with -DSQLITE_DEFAULT_FOREIGN_KEYS=1.
3.6.19以前的版本不支持
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 (2009-10-14) - 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).
如果当前版本支持Foreign Key，必须打开Foreign key constraints开关,因为SQLite默认Foreign key constraints是不可用的（为了保持兼容性）
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (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.)
如何查询Foreign key constraints enabled
The application can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled. The following command-line session demonstrates this:
如何查询版本？
解析db文件，文件Header标明了当前版本
The 4-byte big-endian integer at offset 96 stores the SQLITE_VERSION_NUMBER value for the SQLite library that most recently modified the database file. The 4-byte big-endian integer at offset 92 is the value of the change counter when the version number was stored. The integer at offset 92 indicates which transaction the version number is valid for and is sometimes called the "version-valid-for number".
The SQLITE_VERSION C preprocessor macro in the sqlite3.h header evaluates to a string literal that is the SQLite version in the format "X.Y.Z" where X is the major version number (always 3 for SQLite3) and Y is the minor version number and Z is the release number. The SQLITE_VERSION_NUMBER C preprocessor macro resolves to an integer with the value (X1000000 + Y1000 + Z) where X, Y, and Z are the same numbers used in SQLITE_VERSION. The SQLITE_VERSION_NUMBER for any given release of SQLite will also be larger than the release from which it is derived. Either Y will be held constant and Z will be incremented or else Y will be incremented and Z will be reset to zero.
System.Data.SQLite ADO.NET
由于foreign key constraint默认是关闭的（假设当前版本>=3.6.19），所以我们必须设置foreign key constraint为enabled。
根据文档给出的意见
run PRAGMA foreign_keys=ON or compile with -DSQLITE_DEFAULT_FOREIGN_KEYS=1
compile with -DSQLITE_DEFAULT_FOREIGN_KEYS=1很麻烦，只能运行PRAGMA foreign_keys=ON来打开。
考虑到PRAGMA的“不确定性”
The PRAGMA foreign_key setting does not persist but you can set it every time the connection is made in the ConnectionString. This allows you to use Visual Studio's table adapters.
为什么The PRAGMA foreign_key setting does not persist？
PRAGMA定义：
The PRAGMA statement is an SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data.
但是
The PRAGMA statement is issued using the same interface as other SQLite commands (e.g. SELECT, INSERT) but is different in the following important respects:
- 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.
- The pragma command is specific to SQLite and is not compatible with any other SQL database engine.
所以网友建议
you can set it every time the connection is made in the ConnectionString
应用到 System.Data.SQLite需要注意的
- Make sure you have the latest version (1.0.73.0) of system.data.sqlite installed (1.0.66.0 will not work). 2. Change your ConnectionString to data source=C:\Dbs\myDb.db;foreign keys=true; (replace C:\Dbs\myDb.db with your sqlite database).
