Menu
I am having problems with NULLABLE foreign keys leading to an SQLiteConstraintException. Foreign keys are enabled by using:
PRAGMA foreign_keys = ON
The tables are created with the following statements:
CREATE TABLE conversation (_id INTEGER PRIMARY KEY);
CREATE TABLE message (_id INTEGER PRIMARY KEY, conversation_id INTEGER, FOREIGN KEY(conversation_id) REFERENCES conversation (_id) ON DELETE CASCADE);
The statements are dumbed down to improve reading.
As you can see, the foreign key 'conversation_id' is declared as NULLABLE, so that I am not forced to link every message to a conversation (for example when a draft message gets created that has no corresponding conversation).
When I try to insert a new message with a conversation_id, everything goes fine. When I try it without I get the following error:
11-07 16:44:55.190 20582-20582/com.miawe.miabe E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.miawe.miabe, PID: 20582
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.miawe.miabe/com.miawe.miabe.gui.MainActivity}: android.database.sqlite.SQLiteConstraintException: foreign key constraint failed (code 19)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2195)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2245)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5017)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteConstraintException: foreign key constraint failed (code 19)
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1365)
at com.miawe.miabe.database.DatabaseDataSource.insert(DatabaseDataSource.java:244)
at com.miawe.miabe.database.DatabaseDataSource.createDummyData(DatabaseDataSource.java:307)
at com.miawe.miabe.gui.MainActivity.onCreate(MainActivity.java:64)
at android.app.Activity.performCreate(Activity.java:5231)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2159)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2245)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5017)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
at dalvik.system.NativeStart.main(Native Method)
It seems like sqlite3 is ignoring my NULL-constraint and forcing every message to have a foreign key. What can I do to make the column conversation_id accept NULL values?
937
What is a Foreign Key with "Set Null on Delete" in SQLite? A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null.
A foreign key containing null values cannot match the values of a parent key, since a parent key by definition can have no null values. However, a null foreign key value is always valid, regardless of the value of any of its non-null parts.
SQLite has supported foreign key constraint since version 3.6. 19. The SQLite library must also be compiled with neither SQLITE_OMIT_FOREIGN_KEY nor SQLITE_OMIT_TRIGGER. To check whether your current version of SQLite supports foreign key constraints or not, you use the following command.
MySQL essentially implements the semantics defined by MATCH SIMPLE , which permits a foreign key to be all or partially NULL . In that case, a (child table) row containing such a foreign key can be inserted even though it does not match any row in the referenced (parent) table.
You need to explicitly pass NULL to the insert.
156
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
