SQLite3“忘记”使用外键(SQLite3 “forgets” to use foreign keys)

 我正在使用Ruby和SQLite3，并且我试图在Sqlite3中使用外键很不幸成功。 根据sqlite3 --version ，版本3.7.13被安装。 据我所知，Sqlite3从版本3.6.x开始支持外键。 
 
 我知道外键默认是关闭的，必须用PRAGMA foreign_keys = ON;激活PRAGMA foreign_keys = ON; 。 在我的Ruby db-create-script中，我正在做这样的事情： 
 
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)
 
 不幸的是，我可以愉快地将行插入带有未知app-id的requests ，它可以工作，但当然不应该。 
 
 有趣的是：直接使用sqlite3 shell，我可以观察到以下行为： 
 
$ 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外壳的情况下，外键在激活它们（并且不退出外壳程序）后工作， 我不允许插入具有未知app_id的行。 

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.
 
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)
 
Unfortunately, I can happily insert rows into requests with unknown app-ids, it works, but of course it shouldn't.
 
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 ?!
 
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.

原文：https://stackoverflow.com/questions/15301643