Pages

Sunday, October 30, 2011

How to enable Foreign Keys in Sqlite3 Java

Recently I have created a database in SQLite with tables that has foreign keys ON DELETE CASCADE actions. To my surprise when I deleted the parent key each row of the child table associated with the parent key are not deleted. The answer is that by default in SQLite  foreign key support is turned off for compatibility. To enable foreign keys using Xerial SQLite JDBC Driver We have to enforce foreign key support every time we make a query.

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

5 comments: