[안드로이드 스튜디오/Kotlin] SQLite foreign key mismatch 오류

오류 코드 및 상황

외래키 설정을 해 준 자식 테이블(detail_goal_time_report_db)의 값을 Delete하거나 Insert 할 때

아래와 같이 foreign key mismatch 오류가 발생했다.

Caused by: android.database.sqlite.SQLiteException: foreign key mismatch - "detail_goal_time_report_db" referencing "detail_goal_db" (code 1 SQLITE_ERROR)

 

---

해결방안

오류 당시 detail_goal_db 테이블과 detail_goal_time_report_db 테이블의 상황은 다음과 같았다.

db!!.execSQL("CREATE TABLE detail_goal_db (detail_goal_name text PRIMARY KEY, " +
                "icon text, count int, big_goal_name text, color text, " +
                "FOREIGN KEY (big_goal_name) REFERENCES big_goal_db(big_goal_name) ON UPDATE CASCADE ON DELETE CASCADE);")

db!!.execSQL("CREATE TABLE detail_goal_time_report_db (detail_goal_name text, " +
                "lock_date DATE, photo_name text, big_goal_name text, is_active INT, is_complete INT, " +
                "FOREIGN KEY (detail_goal_name, big_goal_name) REFERENCES detail_goal_db(detail_goal_name, big_goal_name) ON UPDATE CASCADE ON DELETE CASCADE);")

이때 외래키로 참조한 big_goal_name이 detail_goal_db에서는 PRIMARY KEY가 아니었기 때문에 오류가 발생했던 것이다...

 

따라서 big_goal_name을 PRIMARY KEY로 갖고 있는 다른 테이블에서 끌어와 외래키 제약 조건을 걸어 해결했다.

db!!.execSQL("CREATE TABLE detail_goal_time_report_db (detail_goal_name text, " +
                "lock_date DATE, photo_name text, big_goal_name text, is_active INT, is_complete INT, " +
                "FOREIGN KEY (detail_goal_name) REFERENCES detail_goal_db(detail_goal_name) ON UPDATE CASCADE ON DELETE CASCADE, " +
                "FOREIGN KEY (big_goal_name) REFERENCES big_goal_db(big_goal_name) ON UPDATE CASCADE ON DELETE CASCADE);")

 

---

참고자료

https://stackoverflow.com/questions/5208245/what-is-causing-foreign-key-mismatch-error

What is causing Foreign Key Mismatch error?

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

SQLite Foreign Key Support