你如何通过 Java 在 SQLite 中强制执行外键约束? [英] How do you enforce foreign key constraints in SQLite through 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，如果可能的话！

广告 2) 您的第一个代码片段将 PRAGMA 作为语句执行，我认为这行不通.根据您的评论，第三个片段不起作用:SQLite 驱动程序将整个字符串解释为数据库的位置，而不是将foreign keys=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.

广告 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)
);

这篇关于你如何通过 Java 在 SQLite 中强制执行外键约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！