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?
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”

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.

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:

database.setForeignKeyConstraintsEnabled(false)
you will see that an exception is thrown due to a transaction already existing.
              
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.
              
Hi @jay
According to the SQLite documentation here, you should be executing the drop first, then the rename:
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.