How to use correctly foreign keys in Android, using SQLite and OrmLite

Question

First of all, I've created the database with this 2 tables: Adresse and Telephones.

I've implemented foreign keys in android using Ormlite, in order to make a connection 1 to many from Adresse to Telephones.

The code for TELEPHONES:

@DatabaseTable(tableName = "TELEPHONES")
public class Telephone {
    public static final String TABLE_NAME = "TELEPHONES";
    public static final String ID_ADRESSE = "ID_ADRESSE";

    @DatabaseField(generatedId = true)
    private Integer _id;

    @DatabaseField(canBeNull = false, foreign = true,columnName = ID_ADRESSE)
    private Adresse adresse; 
        .
        .
        .
        .
    public Adresse getAdresse() {
        return adresse;
    }

    public void setAdresse(Adresse adresse) {
        this.adresse = adresse;
    }  
}

The code for ADRESSE:

@DatabaseTable(tableName = "ADRESSE")
public class Adresse {

    public static final String TABLE_NAME = "ADRESSE";
    public static final String ID = "_id";

    @DatabaseField(generatedId = true)
    private Integer _id;

    @ForeignCollectionField(eager = false)
    private ForeignCollection<Telephone> telephones;
}

I've also read that the SQLite database implements foreign keys, but you have to enable them each time you open the database , to modify it. So I've added this code:

.
.
.
    public void openDataBase() {
        // Open the database
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READWRITE);
        if (!myDataBase.isReadOnly()) {
            myDataBase.execSQL("PRAGMA foreign_keys=ON;");
        } else {
            System.out.println("database is open as read only");
        }
    }
.
.
.

The problem is that , normally if I want to delete an adresse which has one or more telephones , the foreign key constraint should not let me do that, but nothing happens. No error, no warning , nothing. And when I look in the database to see if I deleted the adresse, I see that the adresse is deleted, and the telephones are still there, with id_adresse linked to the one I've deleted.

I've open the database with SQLite Manager from Firefox, and when I try to do the same thing, an error occurs telling me , that can't do that due to foreign key....

Almost forgot:

The code for the table Telephone in Sqlite:

CREATE TABLE "TELEPHONES" 
("_id" INTEGER PRIMARY KEY  NOT NULL,"ID_ADRESSE" INTEGER REFERENCES ADRESSE(_id))

So my final question is:

What I've done wrong, because in Android I can delete the adresse?

What I want is to implement the foreign keys correctly, not be able to delete the adresse without deleting first the telephone related to the adresse.

UPDATE: I tried doing this:

public void openDataBase() {
    // Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null,
            SQLiteDatabase.OPEN_READWRITE);
    if (!myDataBase.isReadOnly()) {
        myDataBase.execSQL("PRAGMA foreign_keys=ON");
        myDataBase.execSQL("DELETE FROM ADRESSE WHERE _id=8");
    } 
}

and it does delete the adresse and also the telephones related.

So why using the ormlite command :

    DeleteBuilder<Adresse, Integer> db = daoAdresse.deleteBuilder();
db.where().eq(Adresse.ID, 8);
daoAdresse.delete(db.prepare());

inside the tranzaction didn't work?

UPDATE 2:

@Override
    public void onOpen(SQLiteDatabase myDatabase){
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READWRITE);
        if (!myDataBase.isReadOnly()) {
            myDataBase.execSQL("PRAGMA foreign_keys=ON");
            System.out.println("open database with pragma on from onOpen");
        } else {
            System.out.println("database is open as read only from onOpen");
        }

    }

I did this, and i get messages that this method is used, instead of the other one, but is just deleting my adresse, without telephones. So I really have no idea. It must be a problem somewhere , and I think it has something to do with DeleteBuilder...

Still searching..

Answer 1

As far as I'm aware foreign key restrictions are supported, but you have to set it explicitly. This answer might help you. Note, as mentioned in the comments there, it only works for Android 2.2 and up.

UPDATE

You haven't defined an action what to do on update/delete. In Telephone, update your column definition to the following:

@DatabaseField(canBeNull = false, foreign = true, columnDefinition = "integer references ADRESSE(_id) on delete cascade")
private Adresse adresse; 

And recreate your database. Note the "on delete cascade"; when a row in ADRESSE is deleted, all related entries in TELEPHONES should be deleted.