# Android SqlCipher Room Migration failed **URL:** \ **Category:** Issues\ **Created:** [September 28, 2020, 1:55am UTC](https://discuss.zetetic.net/t/android-sqlcipher-room-migration-failed/4606 "2020-09-28T01:55:18Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![jay](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@jay](https://discuss.zetetic.net/u/jay)\ **Post date:** [September 28, 2020, 1:55am UTC](https://discuss.zetetic.net/t/android-sqlcipher-room-migration-failed/4606/1 "2020-09-28T01:55:18Z")
Hi, we are just switching our db to sqlcipher but found that foreign key update failed during migration. We were using Android Room so we followed the guide here: [How to apply SQLCipher to pre-existing Room database?](https://discuss.zetetic.net/t/how-to-apply-sqlcipher-to-pre-existing-room-database/4320/2) and encrypt our db using SQLCipherUtils.java provided by the link. Everything seems working fine until migration. In our migration, we have a tableA which has a foreign key point to tableB, and for some reason we need to recreate tableB so we do something like: private val MIGRATION\_2\_3: Migration = object : Migration(2, 3) { override fun migrate(database: SupportSQLiteDatabase) { database.execSQL(“PRAGMA foreign\_keys=OFF;”) database.execSQL(“CREATE TABLE TABLEB\_NEW (id INTEGER primary key autoincrement NOT NULL, name TEXT NOT NULL)”) database.execSQL(“INSERT INTO TABLEB\_NEW SELECT id, name FROM TABLEB;”) database.execSQL(“ALTER TABLE TABLEB RENAME TO \_OLD\_TABLE”) database.execSQL(“ALTER TABLE TABLEB\_NEW RENAME TO TABLEB”) database.execSQL(“DROP TABLE \_OLD\_TABLE”) database.execSQL(“PRAGMA foreign\_keys=ON;”) } } but compiler throws exception says TableA’s foreign key is still pointing to “\_OLD\_TABLE” which should have been updated to the new TABLEB. the “PRAGMA foreign\_key” command was working fine with the normal Room migration until I have encrypted the db. Any one got any clue how to fix this? Thanks sqlcipher version: implementation “net.zetetic:android-database-sqlcipher:4.4.0” ---
**Author:** ![developernotes](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/developernotes/32/1309_2.png) [@developernotes](https://discuss.zetetic.net/u/developernotes)\ **Post date:** [September 30, 2020, 9:43pm UTC](https://discuss.zetetic.net/t/android-sqlcipher-room-migration-failed/4606/2 "2020-09-30T21:43:43Z")
Hi @jay Is this behavior something you are able to easily reproduce within the SQLCipher for Android [test suite](https://github.com/sqlcipher/sqlcipher-android-tests)? There is a separate package of tests which provide support for running tests with Room. We would be happy to review a reproduction case if you can provide that. Thanks ---
**Author:** ![jay](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@jay](https://discuss.zetetic.net/u/jay)\ **Post date:** [October 4, 2020, 10:42am UTC](https://discuss.zetetic.net/t/android-sqlcipher-room-migration-failed/4606/3 "2020-10-04T10:42:50Z")
Hi @developernotes , it seems pretty hard to reproduce with the test suite so I have created a sample project for you to test. > **[PandaGeek1024/RoomSqlCipher](https://github.com/PandaGeek1024/RoomSqlCipher)** > > Contribute to PandaGeek1024/RoomSqlCipher development by creating an account on GitHub. Steps to reproduce: 1. Checkout the repo and switch to master branch. 2. run the project and install it to a device/emulator for DB version 1 3. Confirm unencrypted DB created(can check with Database inspector) 4. Switch to main branch and run the project again 5. app crash during migration for foreign key not updated. Temporarily workaround: In class WordRoomDatabase.kt, uncomment line 139-158 to recreate tables that with foreignkey reference to other tables and go through the above steps again. Please let me know if it doesn’t work. Cheers. ---
**Author:** ![developernotes](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/developernotes/32/1309_2.png) [@developernotes](https://discuss.zetetic.net/u/developernotes)\ **Post date:** [October 5, 2020, 10:04pm UTC](https://discuss.zetetic.net/t/android-sqlcipher-room-migration-failed/4606/4 "2020-10-05T22:04:48Z")
Hi @jay Did this behavior work for you prior to using SQLCipher, but while still using Room? From what I can tell, Room is starting a transaction prior to the call into `migrate`, thus you aren’t able to disable the foreign key constraints as you are. For example, if you call the following: ```auto database.setForeignKeyConstraintsEnabled(false) ``` you will see that an exception is thrown due to a transaction already existing. ---
**Author:** ![jay](https://avatars.discourse-cdn.com/v4/letter/j/ed8c4c/32.png) [@jay](https://discuss.zetetic.net/u/jay)\ **Post date:** [October 6, 2020, 12:18am UTC](https://discuss.zetetic.net/t/android-sqlcipher-room-migration-failed/4606/5 "2020-10-06T00:18:21Z")
Hi @developernotes It did work prior to using SQLCipher with Room, with these magic commands in my migration code: database.execSQL(“PRAGMA foreign\_keys=OFF;”) database.execSQL(“PRAGMA foreign\_keys=ON;”) You can try it by commenting out the sqlcipher encryption code in WordRoomDatabase class on main branch and just let it run as a normal unencrypted room database, the foreignkeys should be updated normally. ---
**Author:** ![developernotes](https://sea2.discourse-cdn.com/flex016/user_avatar/discuss.zetetic.net/developernotes/32/1309_2.png) [@developernotes](https://discuss.zetetic.net/u/developernotes)\ **Post date:** [October 7, 2020, 5:00pm UTC](https://discuss.zetetic.net/t/android-sqlcipher-room-migration-failed/4606/6 "2020-10-07T17:00:44Z")
Hi @jay According to the SQLite documentation [here](https://sqlite.org/lang_altertable.html#caution), you should be executing the drop first, then the rename: ```auto database.execSQL("DROP TABLE " + TABLE_COOLERS_CACHE) database.execSQL("ALTER TABLE " + TABLE_COOLERS_CACHE + "_NEW" + " RENAME TO " + TABLE_COOLERS_CACHE) ``` When you adjust as above, the migration completes successfully with SQLCipher.