삽질 - SQLite Add Foreign Key

SQLite는 간단하고 가볍지만 기능이 제한적이다. 그 예로 ALTER TABLE로 가능한 기능이 RENAME TABLE, RENAME COLUMN, ADD NEW COLUMN밖에 없고 Foreign Key를 추가할 수 없다.

=> Foreign Key를 추가하는 방법은 새로운 Table을 만들어서 지정하는 법이 있다.

---

Example (Room Migration)

        private val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL("CREATE TABLE Drawer(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL)")
                database.execSQL("CREATE UNIQUE INDEX index_Drawer_name ON Drawer(name)")
                database.execSQL("INSERT INTO Drawer(name) VALUES('')")

                database.execSQL("CREATE TABLE ToDoTemp(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, drawerName TEXT NOT NULL, description TEXT NOT NULL, isFinished INTEGER NOT NULL, isOnTop INTEGER NOT NULL, isSticky INTEGER NOT NULL, hasTerm INTEGER NOT NULL, _begin INTEGER NOT NULL, _end INTEGER NOT NULL)")

                database.query("SELECT * FROM ToDo").also { cursor ->
                    while (cursor.moveToNext()) {
                        val contentValues = ContentValues().apply {
                            put("title", cursor.getString(cursor.getColumnIndex("title")))
                            put("drawerName", "")
                            put("description", cursor.getString(cursor.getColumnIndex("description")))
                            put("isFinished", cursor.getInt(cursor.getColumnIndex("isFinished")))
                            put("isOnTop", cursor.getInt(cursor.getColumnIndex("isOnTop")))
                            put("isSticky", cursor.getInt(cursor.getColumnIndex("isSticky")))
                            put("hasTerm", cursor.getInt(cursor.getColumnIndex("hasTerm")))
                            put("_begin", cursor.getLong(cursor.getColumnIndex("begin")))
                            put("_end", cursor.getLong(cursor.getColumnIndex("end")))
                        }

                        database.insert("ToDoTemp", SQLiteDatabase.CONFLICT_NONE, contentValues)
                    }
                }

                database.execSQL("DROP TABLE ToDo")
                database.execSQL("CREATE TABLE ToDo(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, title TEXT NOT NULL, drawerName TEXT NOT NULL, description TEXT NOT NULL, isFinished INTEGER NOT NULL, isOnTop INTEGER NOT NULL, isSticky INTEGER NOT NULL, hasTerm INTEGER NOT NULL, _begin INTEGER NOT NULL, _end INTEGER NOT NULL, CONSTRAINT drawerName FOREIGN KEY(drawerName) REFERENCES Drawer(name) ON DELETE CASCADE ON UPDATE CASCADE)")
                database.query("SELECT * FROM ToDoTemp").also { cursor ->
                    while (cursor.moveToNext()) {
                        val contentValues = ContentValues().apply {
                            put("title", cursor.getString(cursor.getColumnIndex("title")))
                            put("drawerName", cursor.getString(cursor.getColumnIndex("drawerName")))
                            put("description", cursor.getString(cursor.getColumnIndex("description")))
                            put("isFinished", cursor.getInt(cursor.getColumnIndex("isFinished")))
                            put("isOnTop", cursor.getInt(cursor.getColumnIndex("isOnTop")))
                            put("isSticky", cursor.getInt(cursor.getColumnIndex("isSticky")))
                            put("hasTerm", cursor.getInt(cursor.getColumnIndex("hasTerm")))
                            put("_begin", cursor.getLong(cursor.getColumnIndex("_begin")))
                            put("_end", cursor.getLong(cursor.getColumnIndex("_end")))
                        }

                        database.insert("ToDo", SQLiteDatabase.CONFLICT_NONE, contentValues)
                    }
                }

                database.execSQL("DROP TABLE ToDoTemp")
            }
        }

---

Reference

sqlite.org/lang_altertable.html

ALTER TABLE