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

问题描述

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 编译的).

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 中强制执行外键约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！