Android Sqlite and foreign keys failing

Question

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?

It was an error in the copy paste with later code (edited)

Addev
– Addev

2012-04-13 15:53:55 +00:00
Commented Apr 13, 2012 at 15:53 — Addev
– Addev, Commented Apr 13, 2012 at 15:53

njzk2 · Accepted Answer · 2012-04-13 16:09:13Z

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);

Graham Borland · Accepted Answer · 2012-04-13 15:57:27Z

1

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

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

instead?

answered Apr 13, 2012 at 15:57

Graham Borland

60.8k21 gold badges144 silver badges184 bronze badges

1 Comment

Addev Over a year ago

No, the field I want to refer is value1. Its true that one error can be that is not primary key, but I'm testing with "CREATE TABLE IF NOT EXISTS table_A (_id long, value1 long, PRIMARY KEY(_id,value1) );" and have the same error

Collectives™ on Stack Overflow

Android Sqlite and foreign keys failing

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related