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..

we can write triggers and enforce the foreign key constraints. — Umesh, Nov 21 '12 at 12:55
Add [`ON DELETE CASCADE`](http://www.sqlite.org/foreignkeys.html) to the foreign key definition if you want to delete stuff in `TELEPHONES` if an address is deleted. — zapl, Nov 21 '12 at 13:01

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 21 '12 at 12:52

Reinier

3,836
1
27
28

I'm using Android 4.0.3 and I wrote that above. I've set "PRAGMA foreign_keys=ON;" each time I open the database. – user1809672 Nov 21 '12 at 12:55
Sorry, overlooked that. Did you by any chance create the table before adding `myDataBase.execSQL("PRAGMA foreign_keys=ON;");` ? – Reinier Nov 21 '12 at 13:00
I implemented this , and nothing happens: the adress is deleted , but the telephones are not. My only guess is that the foreign keys are not turned on with that command, or I should put that command somewhere else.... – user1809672 Nov 21 '12 at 13:26
@user1809672 I have working foreign keys and use `db.execSQL("PRAGMA foreign_keys = ON")` (I doubt the additional `;` changes anything, but feel free to try) inside `SQLiteOpenHelper#onOpen` which does about the same as your `openDataBase`. If the db has foreign key constraints that are in fact violated by OrmLite then the only way that can happen is that the pragma was not set or was turned off. Check that OrmLite uses your method each time it opens a new connection to the db – zapl Nov 21 '12 at 13:37
Did you re-create your tables? – Reinier Nov 21 '12 at 13:40
I re-created the tables, and nothing... I've also added on cascade delete to table telephones in sqlite : CREATE TABLE "TELEPHONES" ("_id" INTEGER PRIMARY KEY NOT NULL ,"ID_ADRESSE" INTEGER REFERENCES ADRESSE(_id) ON DELETE CASCADE ). – user1809672 Nov 21 '12 at 13:50
In SQLite Manager, when I'm trying to delete the adresse, is also deleting the telephones, so is working correctly, but in Android just don't want to. – user1809672 Nov 21 '12 at 13:54
For zapl: I've tried without ; , the same thing. I've put a log message, and everytime I want to write in the database , the method is used. – user1809672 Nov 21 '12 at 13:57
Like zapl suggests, try to move the call db.execSQL(...) to your `SQLiteOpenHelper`'s `onOpen()`-method. http://code.google.com/p/android/issues/detail?id=11607 Other than that, I'm fresh out of ideas. – Reinier Nov 21 '12 at 14:17
My guess is because `DeleteBuilder` does not invoke your `openDataBase()`-method, which is why "PRAGMA foreign_keys=ON" is not set, which in turn causes the foreign key constraints not to be enforced. Did you put it in SQLiteOpenHelper#onOpen like @zapl suggests? – Reinier Nov 21 '12 at 16:27

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

1 Answers1

UPDATE