SQLite多对多关系? [英] SQLite many-to-many relationship?

问题描述

我正在尝试使用foo和bar以及它们之间的多对多关系来建立一个SQLite3数据库.这是到目前为止我得到的:

I'm trying to set up a SQLite3 database with foos and bars and a many-to-many relation between them. This is what I've got so far:

CREATE TABLE foo(
    id INTEGER PRIMARY KEY NOT NULL,
    foo_col INTEGER NOT NULL
);
CREATE TABLE bar(
    id INTEGER PRIMARY KEY NOT NULL,
    bar_col TEXT NOT NULL
);
CREATE TABLE foobar(
    foo_id INTEGER,
    bar_id INTEGER,
    FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE CASCADE,
    FOREIGN KEY(bar_id) REFERENCES bar(id) ON DELETE CASCADE
);
CREATE INDEX fooindex ON foobar(foo_id);
CREATE INDEX tagindex ON foobar(tag_id);

...但是它似乎不起作用.我可以从foo中删除一行，它不会影响foobar.我在做什么错了?

...but it doesn't seem to be working. I can delete a row from foo and it doesn't affect foobar. What am I doing wrong?

推荐答案

从本网站摘录， http://www.sqlite.org/foreignkeys.html .

假定在编译库时启用了外键约束，则应用程序仍必须在运行时使用PRAGMA foreign_keys命令将其启用.例如:

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;

默认情况下，外键约束是禁用的(为了向后兼容)，因此必须分别为每个数据库连接分别启用. (但是请注意，SQLite的未来版本可能会更改，从而默认情况下会启用外键约束.谨慎的开发人员不会对默认情况下是否启用外键做出任何假设，而是根据需要启用或禁用它们.)应用程序还可以使用PRAGMA foreign_keys语句来确定当前是否启用了外键.以下命令行会话对此进行了演示:

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

提示:如果命令"PRAGMA foreign_keys"不返回任何数据，而不是包含"0"或"1"的单行，则您正在使用的SQLite版本不支持外键(可能是因为它早于3.6) .19或因为它是使用已定义的SQLITE_OMIT_FOREIGN_KEY或SQLITE_OMIT_TRIGGER编译的.)

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).

在多语句事务的中间(SQLite不在自动提交模式时)，不能启用或禁用外键约束.尝试这样做不会返回错误.它根本没有效果.

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多对多关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！