SQLite - Deleting a row that is referenced as a foreign key
While trying to delete a row that is referenced as a foreign key (for experimental purposes), I get a runtime exception. I understand that I am trying to violate the foreign key constraint, I am just confused about the logcat message because it refers to a completely different part of my code.
This is my SQLiteOpenHelper's onCreate method:
@Override
public void onCreate(SQLiteDatabase db) {
this.db = db;
final String SQL_CREATE_CATEGORIES_TABLE = "CREATE TABLE " +
CategoriesTable.TABLE_NAME + "( " +
CategoriesTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
CategoriesTable.COLUMN_NAME + " TEXT " +
")";
final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
QuestionsTable.TABLE_NAME + " ( " +
QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
QuestionsTable.COLUMN_QUESTION + " TEXT, " +
QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
QuestionsTable.COLUMN_ANSWER_NR + " INTEGER, " +
QuestionsTable.COLUMN_DIFFICULTY + " TEXT, " +
QuestionsTable.COLUMN_CATEGORY + " INTEGER, " +
"FOREIGN KEY(" + QuestionsTable.COLUMN_CATEGORY + ") REFERENCES " +
CategoriesTable.TABLE_NAME + "(" + QuestionsTable._ID + ")" +
")";
db.execSQL(SQL_CREATE_CATEGORIES_TABLE);
db.execSQL(SQL_CREATE_QUESTIONS_TABLE);
fillCategoriesTable();
fillQuestionsTable();
}
Here I am filling the table with the initial questions, and then I try to delete a row from the categories table, to violate the foreign key constraint on purpose (to test it):
private void fillQuestionsTable() {
Question q1 = new Question("Easy: A is correct",
"A", "B", "C", 1, Question.DIFFICULTY_EASY, 1);
addQuestion(q1);
Question q2 = new Question("Medium: B is correct",
"A", "B", "C", 2, Question.DIFFICULTY_MEDIUM, 2);
addQuestion(q2);
Question q3 = new Question("Medium: C is correct",
"A", "B", "C", 3, Question.DIFFICULTY_MEDIUM, 3);
addQuestion(q3);
Question q4 = new Question("Hard: A is correct",
"A", "B", "C", 1, Question.DIFFICULTY_HARD, 4);
addQuestion(q4);
Question q5 = new Question("Hard: B is correct",
"A", "B", "C", 2, Question.DIFFICULTY_HARD, 5);
addQuestion(q5);
Question q6 = new Question("Hard: C is correct",
"A", "B", "C", 3, Question.DIFFICULTY_HARD, 6);
addQuestion(q6);
db.execSQL("DELETE FROM " + CategoriesTable.TABLE_NAME + " WHERE ID = 1");
}
I get a runtime exception. Please notice that I try to violate the foreign key constraint on purpose and just want to understand the exception.
My first question is:
1) The crash is because of the deletion, but why does logcat only talk about adding the questions to category 4,5 and 6 (which are not in my categories table in the first place)?
Error inserting difficulty=Hard option1=A question=Hard: C is correct category_id=6 answer_nr=3 option3=C option2=B android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)
Why does it not talk about deleting a referenced column as I would expect?
2) Why does it throw a runtime exception, whereas trying to add a question that has a category which does not exist in the categories table, just ignores the SQLite command, but doesn't crash the app? Is a runtime exception the expected behavior when violating a foreign key constraint by trying to delete a referenced column?
1) The crash is because of the deletion, but why does logcat only talk about adding the questions to category 4,5 and 6 (which are not in my categories table in the first place)?
No. The Crash, or at least the message shown, is because of what it says: a FOREIGN KEY constraint violation, which happens before the deletion (in addQuestion(q6);) and thus the deletion never happens.
Explaining the FOREIGN KEY constraint and why it failed is a little difficult because it appears that you have mixed up table columns. However, my guess is that that itself is not an issue.
More specifically you define a FOREIGN KEY constraint using
FOREIGN KEY(column_in_this_table_which_you_have_right)
REFERENCES the_other_table_which_you_have_right
(column_in_the_other_table_????)
For the column in the other table you have QuestionsTable._ID it should be CategoriesTable._ID. However, they are probably called the same. So this doesn't cause an issue.
The error message includes category_id=6
Assuming they both resolve to ID then you are effectively saying
Only allow the insertion of a row in the questions table if the category_id* column of the **questions table matches (can reference) a row in the categories table where the ID column is the same as the category_id (i.e. 6).
In short, the displayed messages says there is no category with an id of 6.
Why does it not talk about deleting a referenced column as I would expect?
Because it crashed before getting to db.execSQL("DELETE FROM " + CategoriesTable.TABLE_NAME + " WHERE ID = 1");
2) Why does it throw a runtime exception, whereas trying to add a question that has a category which does not exist in the categories table, just ignores the SQLite command, but doesn't crash the app?
Because in all likelihood your App will crash in numerous places without. You are saying this is something pretty important to the design. I have little doubt that if the ignore option were the case you'd be far more contrary to that (i.e. best option available nobody is forcing you to define the constraint). Although if that's what you want you may wish to check out 4.2 Deferred Foreign Key Constraints
Is a runtime exception the expected behavior when violating a foreign key constraint by trying to delete a referenced column?
To reiterate you didn't get that far.
However, you may wish to check out4.3 ON DELETE and ON UPDATE Actions
Edit - The Confusion Explained.
In short, the crash is actually due to the DELETE constraint. The INSERT constraints are trapped but displayed as the standard insert method is being utilized. The log, if you look at it carefully, will show this, as is explained below.
Here's an example based upon your code that I put together:-
04-11 21:47:10.241 1927-1927/? E/SQLiteDatabase: Error inserting option1=A category_id=6 option2=B option3=C difficulty=Hard answer_nr=3 question=Hard: C is correct
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:775)
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.insert(SQLiteDatabase.java:1339)
at askit.questionaire.DatabaseHelper.addQuestion(DatabaseHelper.java:70)
at askit.questionaire.DatabaseHelper.fillQuestionsTable(DatabaseHelper.java:97)
at askit.questionaire.DatabaseHelper.onCreate(DatabaseHelper.java:52)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
at askit.questionaire.DatabaseHelper.<init>(DatabaseHelper.java:17)
at askit.questionaire.MainActivity.onCreate(MainActivity.java:14)
at android.app.Activity.performCreate(Activity.java:5008)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
at android.app.ActivityThread.access$600(ActivityThread.java:130)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4745)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
at dalvik.system.NativeStart.main(Native Method)
04-11 21:47:10.245 1927-1927/? D/AndroidRuntime: Shutting down VM
04-11 21:47:10.245 1927-1927/? W/dalvikvm: threadid=1: thread exiting with uncaught exception (group=0xa6294288)
04-11 21:47:10.245 1927-1927/? E/AndroidRuntime: FATAL EXCEPTION: main
java.lang.RuntimeException: Unable to start activity ComponentInfo{askit.questionaire/askit.questionaire.MainActivity}: android.database.sqlite.SQLiteConstraintException: foreign key constraint failed (code 19)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
at android.app.ActivityThread.access$600(ActivityThread.java:130)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4745)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteConstraintException: foreign key constraint failed (code 19)
at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:727)
at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1665)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
at askit.questionaire.DatabaseHelper.fillQuestionsTable(DatabaseHelper.java:99)
at askit.questionaire.DatabaseHelper.onCreate(DatabaseHelper.java:52)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
at askit.questionaire.DatabaseHelper.<init>(DatabaseHelper.java:17)
at askit.questionaire.MainActivity.onCreate(MainActivity.java:14)
at android.app.Activity.performCreate(Activity.java:5008)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
at android.app.ActivityThread.access$600(ActivityThread.java:130)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4745)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
at dalvik.system.NativeStart.main(Native Method)
You are seeing the equivalent of:-
04-11 21:47:10.241 1927-1927/? E/SQLiteDatabase: Error inserting option1=A category_id=6 option2=B option3=C difficulty=Hard answer_nr=3 question=Hard: C is correct
android.database.sqlite.SQLiteConstraintException: foreign key constraint failed (code 19)
However, this doesn't result in the App crashing.
- If you run the App, after deleting the database, with the DELETE commented out and look at the log you will see the same in the log, even though the App doesn't crash.
What you need to do is continue through the log and you will find the equivalent of:-
04-11 21:47:10.245 1927-1927/? D/AndroidRuntime: Shutting down VM
04-11 21:47:10.245 1927-1927/? W/dalvikvm: threadid=1: thread exiting with uncaught exception (group=0xa6294288)
04-11 21:47:10.245 1927-1927/? E/AndroidRuntime: FATAL EXCEPTION: main
java.lang.RuntimeException: Unable to start activity ComponentInfo{askit.questionaire/askit.questionaire.MainActivity}: android.database.sqlite.SQLiteConstraintException: foreign key constraint failed (code 19)
This is the crash i.e. the FATAL EXCEPTION:
If you continue, you will see caused by, this is where you look so you have:-
Caused by: android.database.sqlite.SQLiteConstraintException: foreign key constraint failed (code 19)
at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:727)
at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1665)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
at askit.questionaire.DatabaseHelper.fillQuestionsTable(DatabaseHelper.java:99)
at askit.questionaire.DatabaseHelper.onCreate(DatabaseHelper.java:52)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
at askit.questionaire.DatabaseHelper.<init>(DatabaseHelper.java:17)
at askit.questionaire.MainActivity.onCreate(MainActivity.java:14)
at android.app.Activity.performCreate(Activity.java:5008)
If you look through the log caused by you will see that the first line that mentions your package is:-
at askit.questionaire.DatabaseHelper.fillQuestionsTable(DatabaseHelper.java:99)
Line 99 (your line number would be different as would be the package) points to
- Note I changed the line to use the CONSTANT (
CategoriesTable._ID) for the category ID column.
- Play Store warning: You must complete the advertising ID declaration before you can release an app that targets Android 13 (API 33)
- How to force landscape mode when using mobile browser (Unity webgl)?
- Facing an emulator issue in Android: "Error: Device is Already Activating"
- android core library compilation
- Why is an in used function still shown in usage.txt of proguard output?
- How to set a SpannableString to an EditText in an Android Material TextInputLayout?
- Android @Override issues
- " java.lang.ClassCastException: android.widget.Button[] cannot be cast to android.widget.Button" i find this issue in runtime in android kotlin
- Android FTPS Upload Issue: '425 Unable to build data connection: TLS session of data connection not resumed'
- error: cannot find symbol when building android app in flutter using pdftron
- how to add text to the video in android?
- How to keep bottom sheet in backstack when navigating forward?
- Custom Rounded Square Progress Indicator with Icon in Jetpack Compose for Android
- Room Can not Update or Delete Entity
- Why do I get a NullPointerException while calling the method?
- Trying to get first character from a string with multiple words where the first can be an Emoji (Android)
- Two-Way databinding in EditText
- DialogFragment with clear background (not dimmed)
- Android Reading PDF files from asstes folder
- Can't use DiceBears with Flutter NetworkImage
- Openweathermap API get HIGH and LOW temperature of the day
- Firebase onMessageReceived not called when app in background
- How do I add PHP files to my android APK file?
- Compiling Android project from terminal
- Action requested: Declare your Ad ID permission
- Module was compiled with an incompatible version of Kotlin. The binary version of its metadata is 1.5.1, expected version is 1.1.15
- Watch Face Format XML string split?
- Android WebView Not showing Anything
- Android WebView gray play button
- Android Paint: .measureText() vs .getTextBounds()