CODEWITHSUNDEEP
androidkotlinandroid-roomdatabase-migration
Vlad Alexeev
Vlad Alexeev

Reputation: 2214

Room migration - how to alter ForeignKey's onDelete?

I Have an @Entity in Room database. It has 2 foreignKeys and look like :

@Entity(
    tableName = "member_table",
    primaryKeys = ["member_chat_id", "member_user_id"],
    foreignKeys = [
        ForeignKey(
            entity = Chat::class,
            parentColumns = arrayOf("chat_id"),
            childColumns = arrayOf("member_chat_id"),
            onDelete = ForeignKey.NO_ACTION
        ),
        ForeignKey(
            entity = User::class,
            parentColumns = arrayOf("user_id"),
            childColumns = arrayOf("member_user_id"),
            onDelete = ForeignKey.NO_ACTION
        )
    ], indices = [
        Index(value = ["member_chat_id", "member_user_id"], unique = true)
    ]
)

Now, I need to change first foreign key to onDelete = ForeignKey.CASCADE in order to delete this entities together with corresponding Chat::class How do I make a migration from onDelete = ForeignKey.NO_ACTION to onDelete = ForeignKey.CASCADE?

Upvotes: 0

Views: 1398

Answers (1)

Bob Snyder
Bob Snyder

Reputation: 38299

The SQLite documentation for ALTER TABLE, in the section titled "Making Other Kinds Of Table Schema Changes", explains that the only schema altering commands directly supported by SQLite are "rename table", "rename column", and "add column".

The documentation also describes the steps needed to make other schema changes, such as the foreign key action you need to make. The basic concept is:

  1. Create a new table with the desired schema ("member_table_new")
  2. Copy the data from the existing table ("member_table") to the new table
  3. Drop the existing table
  4. Rename the new table, i.e. "member_table_new" to "member_table"

An example of this (in Java) is in the Room Sample.

For your change, the migration is something like the following. If member_table has more fields, add them. Also remember to add the migration to your database build statement.

// TODO Change version numbers as needed
val MIGRATION_4_5 = object : Migration(4, 5) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Create the new table with "ON DELETE CASCADE" for member_chat_id foreign key action
        database.execSQL("""
            CREATE TABLE `member_table_new` (
            `member_chat_id` INTEGER NOT NULL,
            `member_user_id` INTEGER NOT NULL,
            PRIMARY KEY(`member_chat_id`, `member_user_id`),
            FOREIGN KEY(`member_chat_id`) REFERENCES `Chat`(`chat_id`) ON UPDATE NO ACTION ON DELETE CASCADE,
            FOREIGN KEY(`member_user_id`) REFERENCES `User`(`user_id`) ON UPDATE NO ACTION ON DELETE NO ACTION )"""
            .trimIndent())

        // Copy the rows from existing table to new table
        database.execSQL("""
            INSERT INTO member_table_new (member_chat_id, member_user_id)
            SELECT member_chat_id, member_user_id FROM member_table"""
            .trimIndent())

        // Remove the old table
        database.execSQL("DROP TABLE member_table");
        // Change the new table name to the correct one
        database.execSQL("ALTER TABLE member_table_new RENAME TO member_table");

    }
}

The SQLite docs say that these operations should be performed in a transaction. Room performs migrations in a transaction, so you do not need to add your own.

When composing the SQL statement for creating the new table, it's helpful to have Room's schema for the table. If you haven't done so already, enable export of the schema information into a JSON file.

Upvotes: 2

Related Questions