play framework - SQLite: Enable Foreign Key

问题:

I have a problem when using SQLite with play, SQLite documentation says that foreign keys are disabled by default, and need to be enabled with each connection.

I am using SQLite with Ebean, in evolution I added this line:

PRAGMA foreign_keys = ON;

but It doesn't work, it only works when I open a connection from terminal or SQLite Client and execute that line (PRAGMA foreign_keys = ON;), I also tried this in the start of the app but with no luck:

Ebean.getServer("mom").createSqlUpdate("PRAGMA foreign_keys = ON;").execute();

So, how to enable foreign keys?

回答1:

You cannot do this via an evolution, as the PRAGMA is tied to a connection, and not to the database.

Calling the SQL on startup will not work for similar reasons: it will enable the PRAGMA only for the connection from the pool you happen to run the query on.

You need to do it for every connection that is being used.

According to the JDBC documentation of Play Framework, you can achieve this by configuring the connection pool to execute the PRAGMA for every new connection in the application.conf file:

db.default.initSQL="PRAGMA foreign_keys = true"

You can substitute the connection/server name default with mom or how ever you configured your JDBC connection.