SQLite3“忘记"使用外键 [英] SQLite3 "forgets" to use foreign keys

问题描述

我将 Ruby 与 SQLite3 一起使用，但不幸的是，我尝试在 Sqlite3 中使用外键没有成功.根据sqlite3 --version，安装了3.7.13版本.据我所知，Sqlite3 从 3.6.x 版本开始支持外键.

I'm using Ruby with SQLite3 and my attempts to use foreign keys in Sqlite3 were unfortunately not successful. According to sqlite3 --version, version 3.7.13 is installed. As far as I know, Sqlite3 supports foreign keys since version 3.6.x.

我知道默认情况下外键是禁用的，必须使用 PRAGMA foreign_keys = ON; 激活.在我的 Ruby db-create-script 中，我正在做这样的事情:

I know that foreign keys are deactivated by default and have to be activated with PRAGMA foreign_keys = ON;. In my Ruby db-create-script, I'm doing something like this:

sql = <<-SQL
  PRAGMA foreign_keys = ON;
  CREATE TABLE apps (
    id ....
  );
  CREATE TABLE requests (
    ...
    app_id INTEGER NOT NULL,
    FOREIGN KEY(app_id) REFERENCES apps(id),
  );
  ...
SQL
db.execute_batch(sql)

不幸的是，我可以很高兴地将行插入到具有未知应用程序 ID 的 requests 中，它可以工作，但当然不应该.

Unfortunately, I can happily insert rows into requests with unknown app-ids, it works, but of course it shouldn't.

有趣:直接使用 sqlite3 shell，我可以观察到以下行为:

Interesting: using the sqlite3 shell directly, I can observe the following behaviour:

$ sqlite3 database.db
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1 // as expected
sqlite> .quit
$ sqlite3 database.db
sqlite> PRAGMA foreign_keys;
0 // off ?!

在不退出 sqlite3 shell 的情况下，外键在激活它们(而不是退出 shell)后正在工作，并且我不允许插入具有未知 app_id 的行.

Without quitting the sqlite3 shell, foreign keys are working after activating them (and not quitting the shell) and I'm not allowed to insert rows with unknown app_ids.

推荐答案

我想我可以回答我自己的问题:文档说:默认情况下禁用外键约束(为了向后兼容)，因此必须为 每个数据库连接分开.烦人，但现在终于可以使用了.

I think I can answer my own question: The documentation says: Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled for each database connection separately. Annoying, but it's finally working now.

这篇关于SQLite3“忘记"使用外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！