How do you enforce foreign key constraints in SQLite through Java?

Solution 1:

Code like this:

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

Does not work. You have to create org.sqlite.SQLiteConfig and set it as properties when call getConnection from DriverManager.

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.

Solution 2:

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