你如何通过 Java 在 SQLite 中强制执行外键约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9774923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you enforce foreign key constraints in SQLite through Java?
提问by Zarjio
It appears that SQLite does not enforce foreign keys by default. I'm using sqlitejdbc-v056.jarand 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.
默认情况下，SQLite 似乎不强制执行外键。我正在使用sqlitejdbc-v056.jar并且我读到使用PRAGMA foreign_keys = ON;将打开外键约束，并且需要在每个连接的基础上打开它。
My question is: what Java statements do I need to execute to turn on this command? I've tried:
我的问题是：我需要执行哪些 Java 语句才能打开此命令？我试过了：
connection.createStatement().execute("PRAGMA foreign_keys = ON");
and
和
Properties properties = new Properties();
properties.setProperty("PRAGMA foreign_keys", "ON");
connection = DriverManager.getConnection("jdbc:sqlite:test.db", properties);
and
和
connection = DriverManager.getConnection("jdbc:sqlite:test.db;foreign keys=true;");
but none of those work. Is there something I am missing here?
但这些都不起作用。有什么我在这里想念的吗？
I've seen this answerand I want to do exactly the same thing, only using JDBC.
我已经看到了这个答案，我想做完全相同的事情，只使用 JDBC。
采纳答案by ChrLipp
When you look at the SQLite Foreign Key Support pageI would interpret that
当您查看SQLite 外键支持页面时，我会解释为
- SQLlite has to be compiled with foreign key support
- You still have to turn it on for each connection with PRAGMA
- You have to define the foreign key as constraint when you create the table
- SQLlite 必须使用外键支持编译
- 您仍然需要为每个与 PRAGMA 的连接打开它
- 创建表时必须将外键定义为约束
Ad 1) Quoted from here:
广告 1) 引自此处：
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.19or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
如果命令“PRAGMA foreign_keys”没有返回数据，而是一行包含“0”或“1”，那么你使用的 SQLite 版本不支持外键（要么因为它比 3.6.19 旧，要么因为它是用 SQLITE_OMIT_FOREIGN_KEY 或 SQLITE_OMIT_TRIGGER 定义编译的）。
What is your result for PRAGMA foreign_keys;?
你的结果是PRAGMA foreign_keys;什么？
Update: from your comment I see you are using 3.6.14.2, this means your version is not supporting foreign key constraints! So you have to update SQLite, if this is possible!
更新：从您的评论中我看到您使用的是 3.6.14.2，这意味着您的版本不支持外键约束！所以你必须更新 SQLite，如果可能的话！
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.
广告 2）您的第一个代码片段将 PRAGMA 作为语句执行，我认为这行不通。根据您的评论，第三个片段不起作用：SQLite 驱动程序将整个字符串解释为数据库的位置，而不是将 "foreign keys=true" 部分作为连接设置"。所以只有第二个片段会起作用。
Ad 3) Did you create the table with foreign key support? Quoted from here:
广告 3）您是否创建了具有外键支持的表？从这里引用：
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
);
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER,
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
回答by Dave
Try
尝试
connection = DriverManager.getConnection("jdbc:sqlite:test.db;foreign keys=true;");
Based on the question you linked, it looks to be a likely candidate.
根据您链接的问题，它看起来很可能是候选人。
回答by LostSalad
I found this question while Googling the same problem. I was using sqlitejdbc-v056.jarfrom Zentus, which uses an older version of the SQLite driver, and doesn't support foreign keys.
我在谷歌搜索同样的问题时发现了这个问题。我使用的sqlitejdbc-v056.jar是Zentus，它使用旧版本的 SQLite 驱动程序，并且不支持外键。
I tried what seems to be the Xerial driver v3.7.2from the Maven Repository
我尝试v3.7.2了Maven 存储库中的 Xerial 驱动程序
I have not researched the difference between these drivers, but a hot-switch between the two didn't break anything and fixed my issue, so, I hope this helps someone.
我没有研究过这些驱动程序之间的区别，但是两者之间的热切换没有破坏任何东西并解决了我的问题，所以，我希望这对某人有所帮助。
回答by toddg
On a linux desktop, when I tried,
在 linux 桌面上，当我尝试时，
connection = DriverManager.getConnection("jdbc:sqlite:/path/to/test.db;foreign keys=true;");
sqlite3 (3.7.13) thought that my database filewas /path/to/test.db;foreign keys=true. This led to the strange, but I guess appropriate, error: table does not exist
sqlite3 (3.7.13) 认为我的数据库文件是/path/to/test.db;foreign keys=true。这导致了奇怪的，但我想合适的错误：表不存在
See how to solve no such table while inserting exception in sqlite data base
I thought I had fixed both of these issues by stuffing the foreign key statement into a property like so:
我以为我已经通过将外键语句填充到这样的属性中来解决这两个问题：
private final Properties connectionProperties = new Properties();
connectionProperties.setProperty("PRAGMA foreign_keys", "ON");
private final String connectionString = String.format("jdbc:sqlite:%s", absolute_path_to_sqlite_db);
Connection connection = DriverManager.getConnection(connectionString, connectionProperties);
But even tho the database name issue was resolved, SQLite was still allowing constraint violations. Some more noodling with Xerial's driver and this is what finally worked:
但即使解决了数据库名称问题，SQLite 仍然允许违反约束。对 Xerial 的驱动程序进行了更多讨论，这就是最终奏效的方法：
private final Properties connectionProperties = new Properties();
SQLiteConfig config = new SQLiteConfig();
config.enforceForeignKeys(true);
connectionProperties = config.toProperties();
private final String connectionString = String.format("jdbc:sqlite:%s", absolute_path_to_sqlite_db);
Connection connection = DriverManager.getConnection(connectionString, connectionProperties);
回答by Cherry
Code like this:
像这样的代码：
DriverManager.getConnection("jdbc:sqlite:some.db;foreign keys=true;")
Does not work.
You have to create org.sqlite.SQLiteConfigand set it as properties when call getConnection from DriverManager.
不起作用。org.sqlite.SQLiteConfig从 DriverManager 调用 getConnection 时，您必须创建并将其设置为属性。
public static final String DB_URL = "jdbc:sqlite:database.db";
public static final String DRIVER = "org.sqlite.JDBC";
public static Connection getConnection() throws ClassNotFoundException {
Class.forName(DRIVER);
Connection connection = null;
try {
SQLiteConfig config = new SQLiteConfig();
config.enforceForeignKeys(true);
connection = DriverManager.getConnection(DB_URL,config.toProperties());
} catch (SQLException ex) {}
return connection;
}
This code taken from this.
这段代码取自这个。
回答by cbhedd
I unfortunately can't comment on the previous poster's answer but as a heads up for anybody else who may come by here, the first code snippet:
不幸的是，我无法评论前一张海报的答案，但作为对可能来到这里的其他人的提醒，第一个代码片段：
connection.createStatement().execute("PRAGMA foreign_keys = ON");
absolutely does work when your version of SQLite is up to date and supports foreign key support.
当您的 SQLite 版本是最新的并且支持外键支持时，绝对可以工作。
回答by lsh
this page was helpful when translating to Clojure, however my solution was different. So, for posterity, even though the OP asked for Java, this is how I did it in Clojure:
此页面在翻译为 Clojure 时很有帮助，但我的解决方案不同。因此，对于后代，即使 OP 要求使用 Java，我在 Clojure 中也是这样做的：
(def db-spec {:connection-uri "jdbc:sqlite:db.sqlite3?foreign_keys=on;"})
(def db-spec {:connection-uri "jdbc:sqlite:db.sqlite3?foreign_keys=on;"})
回答by Alex Behemoth
I use mybatis, in mybatis-config.xml :<property name="url" value="jdbc:sqlite:example.db?foreign_keys=on;"/>
that's work for mybatis framework.
我在 mybatis-config.xml 中使用 mybatis：<property name="url" value="jdbc:sqlite:example.db?foreign_keys=on;"/>
这适用于 mybatis 框架。
