如何通过Java在SQLite中执行外键约束？ [英] How do you enforce foreign key constraints in SQLite through Java?

问题描述

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

我的问题是：我需要执行哪些Java语句来打开此命令？我试过：

$ p $ 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;）; 
  

但这些工作都没有。有什么我在这里失踪？

我见过和我想要做的完全一样的事情，只使用JDBC。

解决方案

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

1. 必须使用外键支持编译SQLlite
   
2. 您仍然必须为每个与PRAGMA连接


3. 在创建表时，必须将外键定义为约束条件
   

广告1）从此处引用：

< blockquote>

如果命令PRAGMA foreign_keys没有返回数据而不是包含0或1的单个行，那么SQLite的版本是usi ng不支持外键（因为它早于3.6.19 ，或者是因为它是使用SQLITE_OMIT_FOREIGN_KEY或SQLITE_OMIT_TRIGGER定义的编译的）。

您的结果是 PRAGMA foreign_keys; ？

更新：从您的评论我看你使用的是3.6.14.2，这意味着你的版本不支持外键约束！所以你必须更新SQLite，如果这是可能的！

广告2）您的第一个代码段执行PRAGMA作为声明，我不认为这将工作。第三个剪辑没有根据您的评论工作： SQLite驱动程序将整个字符串解释为数据库的位置，而不是将外键= true部分作为连接设置。只有第二个片段可以工作。

广告3）您是否使用外键支持来创建表格？引用自
$ b

  CREATE TABLE艺术家（
 artistid INTEGER PRIMARY KEY，
 artistname TEXT 
）; 
 CREATE TABLE track（
 trackid INTEGER，
 trackname TEXT，
 trackartist INTEGER，
 FOREIGN KEY（trackartist）参考艺术家（artistid）
）; 
  

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.

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");

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 answer and I want to do exactly the same thing, only using JDBC.

解决方案

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

1. SQLlite has to be compiled with foreign key support
2. You still have to turn it on for each connection with PRAGMA
3. You have to define the foreign key as constraint when you create the table

Ad 1) Quoted from here:

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

What is your result for 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!

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) 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屋！