Android-Room如何添加外键引用进行数据迁移

Question

我更新了我的一个房间实体以包含一个外键。我现在必须在我的数据迁移中包含此更改，但不确定 SQL 查询。

我已经尝试了以下但它没有编译:

private val MIGRATION_1_2 = object: Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE `Entity` ADD FOREIGN KEY(`parent_id`) 
                REFERENCES `Entity`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE)")
    }
}

Answer 1

在 SQLite 上，修改表以添加外键是不可能的，至少根据这个 SQLite documentation :

Only the RENAME TABLE, ADD COLUMN, and RENAME COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

考虑到这一点，我认为迁移数据库的正确方法是:

1. 创建新的临时表，
2. 将值从旧表复制到临时表，
3. 放弃旧表，
4. 将临时表重命名为旧表名。

在您的情况下，这可能看起来像这样:

override fun migrate(database: SupportSQLiteDatabase) {
    // Create a new translation table
    database.execSQL("CREATE TABLE IF NOT EXISTS `Entity_new` (" +
        "`old_column_1` TEXT NOT NULL, " +
        "`old_column_2` TEXT NOT NULL, " +
        "`parent_id` INTEGER, " +
        "`entity_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
        "FOREIGN KEY(`parent_id`) REFERENCES `Entity`(`entity_id`) ON UPDATE NO ACTION ON DELETE CASCADE )")
    // Copy the data
    database.execSQL("INSERT INTO `Entity_new` (old_column_1, old_column_2, entity_id) " +
        "SELECT old_column_1, old_column_2, entity_id " +
        "FROM Entity")
    // Remove old table
    database.execSQL("DROP TABLE Entity")
    // Change name of table to correct one
    database.execSQL("ALTER TABLE Entity_new RENAME TO Entity")
}

免责声明:我采用了这个非常有用的代码 PersistenceMigrationSample project .