android - 房间 : Renaming Table doesn't update Foreign Key Table Reference

Question

object Migration1to2 : Migration(1, 2) {

    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE photos RENAME TO media")
    }
}

我正在使用上面的 Migration 对象执行迁移，但得到以下 IllegalStateException:

java.lang.IllegalStateException: Migration didn't properly handle tags(com.curator.android.storage.room.model.Tag).
     Expected:
    TableInfo{name='tags', columns={groupId=Column{name='groupId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0}, photoId=Column{name='photoId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, accuracy=Column{name='accuracy', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0}, entityId=Column{name='entityId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1}, parentId=Column{name='parentId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}}, foreignKeys=[ForeignKey{referenceTable='media', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[photoId], referenceColumnNames=[id]}], indices=[Index{name='index_tags_photoId', unique=false, columns=[photoId]}, Index{name='index_tags_name_photoId', unique=true, columns=[name, photoId]}, Index{name='index_tags_name', unique=false, columns=[name]}]}
     Found:
    TableInfo{name='tags', columns={groupId=Column{name='groupId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, name=Column{name='name', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0}, photoId=Column{name='photoId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, accuracy=Column{name='accuracy', type='REAL', affinity='4', notNull=false, primaryKeyPosition=0}, entityId=Column{name='entityId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1}, parentId=Column{name='parentId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}}, foreignKeys=[ForeignKey{referenceTable='photos', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[photoId], referenceColumnNames=[id]}], indices=[Index{name='index_tags_photoId', unique=false, columns=[photoId]}, Index{name='index_tags_name_photoId', unique=true, columns=[name, photoId]}, Index{name='index_tags_name', unique=false, columns=[name]}]}

区别在于referenceTable

foreignKeys=[ForeignKey{referenceTable='media', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[photoId], referenceColumnNames=[id]}]
foreignKeys=[ForeignKey{referenceTable='photos', onDelete='CASCADE', onUpdate='CASCADE', columnNames=[photoId], referenceColumnNames=[id]}]

Answer 1

在 SQLite 中对外键的支持有限[1]。如果你真的想重命名表，你必须创建一个带有更新外键的新表并复制旧表的内容。 (根据 [2] 调整)

object Migration1to2 : Migration(1, 2) {

    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("PRAGMA foreign_keys = OFF;")
        database.beginTransaction()
        database.execSQL("ALTER TABLE photos RENAME TO media; ")
        database.execSQL("ALTER TABLE tags RENAME TO tagsOld; ")
        // database.execSQL("DROP INDEX `indexName`") // optional
        database.execSQL("CREATE TABLE `tags` ( ... ); ") // you can copy that from scheme 2.json
        // database.execSQL("CREATE INDEX `indexName` ON `tags` ( ... )") // optional - you can copy that from scheme 2.json
        database.execSQL("INSERT INTO tags SELECT * FROM tagsOld; ")
        database.execSQL("DROP TABLE tagsOld; ")
        database.setTransactionSuccessful()
        database.endTransaction()
        database.execSQL("PRAGMA foreign_keys = ON;")
    }
}

[1] https://www.sqlite.org/lang_altertable.html

[2] https://www.techonthenet.com/sqlite/foreign_keys/drop.php