Replace causing Foreign Key mismatch in Android

I have the following database in Android:

Table A: [_id, value1]
Table B: [_id, fk_tablea, value2]

With code:

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 ,fk_tablea long  , value2 long,"
        + "FOREIGN KEY (fk_tablea) REFERENCES table_A (value1) ON DELETE CASCADE);");

And if I make (with the table empty):

ContentValues values= new ContentValues();
values.put("_id",1);
values.put("value1",900);
mDb.replace("table_A", null, values);

or

mDb.execSQL("INSERT OR REPLACE  INTO table_A(_id,value1) VALUES (1,900)");

of

mDb.insertWithOnConflict("table_A", null, values, SQLiteDatabase.CONFLICT_REPLACE);

It crashes with an exception Caused by: android.database.sqlite.SQLiteException: foreign key mismatch: , while compiling: INSERT OR REPLACE INTO table_A(_id,value1) VALUES (2,900) . But if instead of replace I use insert

ContentValues values= new ContentValues();
values.put("_id",1);
values.put("value1",900);
mDb.insert("table_A", null, values);

Works correctly, why is that happening? and how can I solve it? Thanks in advance

Answers


The foreign key in table_B refers to a non-unique field value1 in table_A. The following demonstrates how that cannot work:

sqlite> pragma foreign_keys=1;
sqlite> create table foo (a int, b int);
sqlite> create table bar (c int, d int, foreign key (c) references foo(b));
sqlite> insert into foo values (1,1);
sqlite> insert into bar values (1,1);
Error: foreign key mismatch
sqlite> create unique index idx1 on foo(b);
sqlite> insert into bar values (1,1);
sqlite> 

Documentation this way:

http://www.sqlite.org/foreignkeys.html#fk_indexes


Need Your Help

How do you send and receive UDP packets in Java on a multihomed machine?

java sockets

I have a machine with VmWare installed which added two extra network interfaces. The OS is Vista. I have two Java applications, one which broadcasts datagrams, and one which receives those datagr...

Jasper-Studio: Custom Template

jaspersoft-studio

a tutorial about how to creat custom templates is available here: http://community.jaspersoft.com/wiki/creating-custom-template-jaspersoft-studio

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.