user6313011 user6313011 - 7 months ago 14
Java Question

Why aren't my foreign keys working?

My create table statement:

CREATE TABLE main_table (
_ID INTEGER PRIMARY KEY AUTOINCREMENT,
account_id INTEGER NOT NULL,
employee_id INTEGER NOT NULL,
rating REAL NOT NULL,
timestamp LONG NOT NULL,
FOREIGN KEY(account_id) REFERENCES ACCOUNTS(_ID),
FOREIGN KEY(employee_id) REFERENCES EMPLOYEES(_ID),
UNIQUE (account_id, employee_id, timestamp));


However now if I do something like

public void deleteAccount(long accountId) {
mDatabase.delete(
ACCOUNTS,
_ID + " = ?",
new String[]{accountId + ""}
);
}


Which is basically
DELETE FROM ACCOUNTS WHERE _ID = some_id
but it isn't cascading through the other tables (employees or main_table) and deleting any records there that link up through the foreign keys.

I do have this code as well:

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


Am I misunderstanding how these work? Isn't this what foreign keys are for? Why isn't it cascading?

Answer

You have to mention explicitly if you want any cascading

Change the query like below

CREATE TABLE main_table (
    _ID INTEGER PRIMARY KEY AUTOINCREMENT, 
    account_id INTEGER NOT NULL, 
    employee_id INTEGER NOT NULL,
    rating REAL NOT NULL, 
    timestamp LONG NOT NULL, 
    FOREIGN KEY(account_id) REFERENCES ACCOUNTS(_ID) ON DELETE CASCADE,
    FOREIGN KEY(employee_id) REFERENCES EMPLOYEES(_ID) ON DELETE CASCADE,
    UNIQUE (account_id, employee_id, timestamp));