read

Foreign keys on database serve the purpose to enforce constraints, to maintain database integrity. Another purpose is that way one may implement "table inheritance" in SQL. You place common set of data in one table, specific info in set of tables and binding them via a foreign key. On Android most common way to operate on SQLite is to derive a class from `SQLiteOpenHelper`

public class ContentHelper extends SQLiteOpenHelper {
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(some create table string);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

This is basic stuff that correctly extends `SQLiteOpenHelper`. `onCreate()` method is called after database file is established and connection open; database is ready to execute SQL queries. Due to that we need to enable foreign keys before executing any query creating a table. It is sufficient to put following code into beginning of this method:

if (!db.isReadOnly()) {
    // Enable foreign key constraints
    db.execSQL("PRAGMA foreign_keys=ON;");
}

The same code might be used on onConfigure(SQLiteDatabase) but this is available since API 16. This method is called when the database connection is being configured, to enable features such as write-ahead logging or foreign key support.

Blog Logo

Marcin Gil


Published

Image

When grass was greener

Rants on programming, photography, life and everything

Back to Overview