2,209
社区成员
发帖
与我相关
我的任务
分享sqlite外码问题
CREATE TABLE users(
uid INTEGER PRIMARY KEY AUTOINCREMENT,
nickname VARCHAR(32) UNIQUE NOT NULL,
pass CHARACTER(32) NOT NULL
);
CREATE TABLE passphrase(
pid INTEGER PRIMARY KEY AUTOINCREMENT,
owner INTEGER NOT NULL,
method VARCHAR(8) NOT NULL,
key VARCHAR(255) NOT NULL,
FOREIGN KEY(owner) REFERENCES users(uid) ON DELETE CASCADE
);
这样的语句外码约束不生效，sqlite的版本是3.6.22貌似已经支持外码了。
比如：
insert into passphrase
values(NULL, 随便一个在users中不存在的id, "aaa", "bbb");
不提示错误。。。。。。
uid INTEGER PRIMARY KEY AUTOINCREMENT,
nickname VARCHAR(32) UNIQUE NOT NULL,
pass CHARACTER(32) NOT NULL
);
CREATE TABLE passphrase(
pid INTEGER PRIMARY KEY AUTOINCREMENT,
owner INTEGER NOT NULL,
method VARCHAR(8) NOT NULL,
key VARCHAR(255) NOT NULL,
FOREIGN KEY(owner) REFERENCES users(uid) ON DELETE CASCADE
);
这样的语句外码约束不生效，sqlite的版本是3.6.22貌似已经支持外码了。
比如：
insert into passphrase
values(NULL, 随便一个在users中不存在的id, "aaa", "bbb");
不提示错误。。。。。。
...全文
请发表友善的回复…
发表回复
skt90 2010-06-19
- 打赏
- 举报
[Quote=引用 1 楼 acmain_chm 的回复:]
引用sqlite的版本是3.6.22貌似已经支持外码了。
参考一下SQLite的文档说明如下。显然不支持。
FOREIGN KEY constraints FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be ……
[/Quote]
你一说我又看了看文档（http://www.sqlite.org/foreignkeys.html#fk_actions），发现了这样一段话：
2. Enabling Foreign Key Support
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. The following command-line session demonstrates this:
sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> PRAGMA foreign_keys = OFF;
sqlite> PRAGMA foreign_keys;
0
Tip: If the command "PRAGMA foreign_keys" returns no data instead of a single row containing "0" or "1", then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.
开启那个选项后，发现约束生效了！！！
还是要谢谢你。
引用sqlite的版本是3.6.22貌似已经支持外码了。
参考一下SQLite的文档说明如下。显然不支持。
FOREIGN KEY constraints FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be ……
[/Quote]
你一说我又看了看文档（http://www.sqlite.org/foreignkeys.html#fk_actions），发现了这样一段话：
2. Enabling Foreign Key Support
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. The following command-line session demonstrates this:
sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> PRAGMA foreign_keys = OFF;
sqlite> PRAGMA foreign_keys;
0
Tip: If the command "PRAGMA foreign_keys" returns no data instead of a single row containing "0" or "1", then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.
开启那个选项后，发现约束生效了！！！
还是要谢谢你。
ACMAIN_CHM 2010-06-19
- 打赏
- 举报
[Quote]sqlite的版本是3.6.22貌似已经支持外码了。[/Quote]
参考一下SQLite的文档说明如下。显然不支持。
FOREIGN KEY constraints FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers. The SQLite source tree contains source code and documentation for a C program that will read an SQLite database, analyze the foreign key constraints, and generate appropriate triggers automatically.
参考一下SQLite的文档说明如下。显然不支持。
FOREIGN KEY constraints FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers. The SQLite source tree contains source code and documentation for a C program that will read an SQLite database, analyze the foreign key constraints, and generate appropriate triggers automatically.
