如何通过 Java 在 SQLite 中强制执行外键约束?

2022-01-20 00:00:00 foreign-keys sqlite jdbc java

默认情况下，SQLite 似乎不强制执行外键.我正在使用 sqlitejdbc-v056.jar 并且我已经使用 PRAGMA foreign_keys =ON; 将打开外键约束，这需要在每个连接的基础上打开.

It appears that SQLite does not enforce foreign keys by default. I'm using sqlitejdbc-v056.jar and I've read that using PRAGMA foreign_keys = ON; will turn on foreign key constraints, and that this needs to be turned on in a per-connection basis.

我的问题是:我需要执行哪些 Java 语句才能打开此命令?我试过了:

My question is: what Java statements do I need to execute to turn on this command? I've tried:

connection.createStatement().execute("PRAGMA foreign_keys = ON");

和

Properties properties = new Properties();
properties.setProperty("PRAGMA foreign_keys", "ON");
connection = DriverManager.getConnection("jdbc:sqlite:test.db", properties);

和

connection = DriverManager.getConnection("jdbc:sqlite:test.db;foreign keys=true;");

但这些都不起作用.我在这里有什么遗漏吗?

but none of those work. Is there something I am missing here?

我已经看到 这个答案和我想做完全相同的事情，只使用 JDBC.

I've seen this answer and I want to do exactly the same thing, only using JDBC.

推荐答案

当您查看 SQLite 外键支持页面时 我会这样解释

When you look at the SQLite Foreign Key Support page I would interpret that

  1. SQLlite 必须使用外键支持编译
  2. 您仍然需要为每次使用 PRAGMA 的连接打开它
  3. 创建表时必须将外键定义为约束

广告 1) 引自此处:

如果命令PRAGMA foreign_keys"没有返回数据，而是返回包含0"或1"的单行，那么您使用的 SQLite 版本不支持外键(或者因为 它比3.6.19 或者因为它是使用 SQLITE_OMIT_FOREIGN_KEY 或 SQLITE_OMIT_TRIGGER 定义编译的).

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

PRAGMA foreign_keys; 的结果是什么?

更新:从您的评论中我看到您使用的是 3.6.14.2，这意味着您的版本不支持外键约束！因此，如果可能的话，您必须更新 SQLite！

Ad 2) 您的第一个代码片段执行 PRAGMA as 语句，我认为这不会起作用.根据您的评论，第三个片段不起作用:SQLite 驱动程序将整个字符串解释为数据库的位置，而不是将外键=true"部分作为连接设置".所以只有第二个片段可以工作.

Ad 2) Your first code snippet executes the PRAGMA as statement, I don't think this will work. The third snipped didn't work based on your comment: the SQLite driver interprets the whole string as the location of the database, instead of taking the "foreign keys=true" part as connection settings". So only the second snippet will work.

Ad 3) 您是否创建了支持外键的表?引用自这里:

Ad 3) Did you create the table with foreign key support? Quoted from here:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

相关文章