CODEWITHSUNDEEP
javaandroidsqlite
Addev
Addev

Reputation: 32221

Android Sqlite and foreign keys failing

I'm testing the foreign keys in Android and I have problems I don't understand:

To create the tables (with hard-coded values)

db.execSQL("CREATE TABLE IF NOT EXISTS table_A ( " +
            "_id long primary key , value1 long  );");
db.execSQL("CREATE TABLE IF NOT EXISTS table_B ( " +
            "_id long primary key , value1fk long  , value2 long,"+
            "FOREIGN KEY (value1fk) REFERENCES table_A (value1) ON DELETE CASCADE);");

then I execute:

    ContentValues values = new ContentValues();
    values.put("_id", 1);
    values.put("value1", 200);
    long result = mDb.insert("table_A", null, values);
    Log.e("","done (" + result + ")");


    values = new ContentValues();
    values.put("_id", 1);
    values.put("value1fk", 200);
    values.put("value2", 10);
    result= mDb.insert("table_B", null, values);
    Log.e("","done (" + result + ")");

The output is 

done(1)
done(-1)

Giving the second insert an foreign key mismatch

E/SQLiteDatabase(25476): Error inserting _id=1 value1fk=200 value2=10
E/SQLiteDatabase(25476): android.database.sqlite.SQLiteException: foreign key mismatch: , while compiling: INSERT INTO table_B(_id,value1fk,value2) VALUES (?,?,?)

Why is that happening?

Upvotes: 1

Views: 2363

Answers (2)

njzk2
njzk2

Reputation: 39386

According to sqlite documentation on foreign keys http://www.sqlite.org/foreignkeys.html you need to either :

  • use the primary key as your foreign key

    FOREIGN KEY (value1fk) REFERENCES table_A (_id)

  • use a Unique column as a foreign key

    CREATE TABLE IF NOT EXISTS table_A (_id long primary key , value1 long UNIQUE);

Upvotes: 10

Graham Borland
Graham Borland

Reputation: 60681

The value_1 field in table_A is not the primary key. Shouldn't it be

FOREIGN KEY (value1fk) REFERENCES table_A (_id)...

instead?

Upvotes: 1

Related Questions