Android SQLiteHelper can't create FOREIGN KEY

Question

I'm having some trouble with SQLite on Android. I try to create my tables that way:

Trade Shows:

String CREATE_TRADE_SHOW_TABLE = "CREATE TABLE tradeShows ( " +
                "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT NOT NULL, " +
                "location TEXT NOT NULL, " +
                "doDate DATE NOT NULL, " +
                "createdDate DATETIME NOT NULL, " +
                "lastModDate DATETIME NOT NULL, " +
                "pictureUrl TEXT, " +
                "picture BLOB )";
        db.execSQL(CREATE_TRADE_SHOW_TABLE);

It's working fine, and then, Vendors:

String CREATE_TRADE_SHOW_TABLE = "CREATE TABLE vendors ( " +
                "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                "tradeShowId INTEGER NOT NULL, " +
                "name TEXT NOT NULL, " +
                "createdDate DATETIME NOT NULL, " +
                "lastModDate DATETIME NOT NULL, " +
                "pictureUrl TEXT, " +
                "picture BLOB," +
                "FOREIGN KEY(tradeShowId) REFERENCES tradeShows(id)" +
                ")";
        db.execSQL(CREATE_TRADE_SHOW_TABLE);

So now my Table Vendors is created, but the tradeShowId FOREIGN KEY is not there. I dont get any error, my table is created just fine, but without the tradeShowId.

What can I be possibly doing wrong?

If you just added the column and the whole column is missing, uninstall the app so the database gets recreated. See [here](http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run) for more. If the column is there but the foreign key is not enforced, see Michal's answer below. — laalto, Mar 17 '14 at 11:36

score 3 · Accepted Answer · answered Mar 17 '14 at 11:34

SQLite supports foreign keys, but not by default.

If you write for API level 16 or higher, you should do it in onConfigure method of the SQLiteOpenHelper class:

@Override
public void onConfigure(SQLiteDatabase db) {
    db.setForeignKeyConstraintsEnabled(true);
}

In the previous API levels it should be done this way:

@Override 
public void onOpen(SQLiteDatabase db) { 
    db.execSQL("PRAGMA foreign_keys = ON;"); 
}

You can read about this in SQLite documentation:

"Foreign key constraints are disabled by default (for backwards compatibility), so [they] must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints [are] enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.)"

Android SQLiteHelper can't create FOREIGN KEY

1 Answers1