Android - Room how to add foreign key reference to data migration

Question

I have updated one of my Room entities to include a foreign key. I must now include this change in my data migration but unsure on the SQL query.

I've tried the following but it doesn't compile:

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

On SQLite, altering a table to add a foreign key is impossible, at least according to this 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.

With that in mind, I think the proper way to migrate the database would be to:

1. Create new temporary table,
2. Copy values from old table to the temporary table,
3. Drop the old table,
4. Rename the temporary table to the old table name.

In your case, this would probably look like this:

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")
}

Disclaimer: I adopted the code from this very useful PersistenceMigrationSample project.