I'm developing an Android 2.2+ app with SQLite db support. I'm trying to
define referential integrity constraints while creating the tables. I've
also enabled the foreign key support by executing db.execSQL( "PRAGMA
foreign_keys=ON;" ); in onCreate and onOpen methods of SQLiteOpenHelper.
Afteer setting up my tables and proper foreign key references, it still
allows me to insert rows in tables where reference records are missing. For
e.g. I've following structure
CREATE TABLE Questions(_id integer primary key,question text not null);
CREATE TABLE Ques_Ans(_id integer primary key autoincrement,qid integer not
null,
aid integer not null,is_correct integer default 0,
FOREIGN KEY (qid) REFERENCES Questions(_id));
and following my data in the table
INSERT INTO Questions VALUES(1, 'Some text');
INSERT INTO Ques_Ans(qid, aid, is_correct) VALUES(20, 1, 0);
If foreign key is properly set on Ques_Ans table, 2nd insert should have
failed as there is no record in Questionstable with id 20, but somehow my
app does not thrown any error and inserts the 2nd insert statement. Can
anybody tell me whats wrong over here or am I missing any configuration
over here? Below is what I've already tested...
- Using sqlite3 tool, switching on PRAGMA foreign_keys=ON; foreign key
works as expected
- Same thing if used via app on Emulator or on Phone does not work
- Insert statements executed using insert() or execSQL(). None of them
throw foreign key constraint failed error
- PRAGMA integrity_check returns ok. So database is not corrupt.
Any help in this regards would be great.
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
