SQLite foreign key constraints

Question

I am using Java with SQLiteJDBC.

I was struggling to understand why my foreign keys were not acting as constraints, until I read the following:

Prior to version 3.6.19, SQLite did not support foreign key constraints.

I then took a closer look at the SQLiteJDBC website and found:

The current version is v056, based on SQLite 3.6.14.2.

There is a solution documented here, although it is a very old article - is it still the best way to proceed? Would other SQLite Java connectors be recommended?

Is this a situation in which upgrading is an expensive operation? Upgrading is the best way, given the newer version provides exactly the functionality you want. — MrGomez
I am very open to alternatives; it's all a learning curve, be it a frustrating one. How do you mean newer version? Edit: I think there's been some confusion - the SQLiteJDBC was built off a version off SQLite prior to foreign key constraints being implemented. — c24w
My mistake. It turns out there isn't a newer version of SQLiteJDBC based on anything above 3.6.19. In fact, it hasn't been updated since 2009. So, that won't work. — MrGomez

Brendan Long Brendan Long · Accepted Answer · 2012-03-22T21:53:58

SQLite support in Java seems to not be so great.

You may be able to rebuild javasqlite against a newer version of SQLite, since it depends on:

SQLite 2.4.12 or higher

Whether it will actually work remains to be seen, but I think SQLite has a fairly stable API.

You could also try to build SQLiteJDBC from source against a newer version of SQLite.

EDIT:

Actually, Xerial SQLiteJDBC has been updated much more recently. Apparently it's a lot slower though since it's pure Java.

SQLite foreign key constraints

1 Answers