SQLiteException no such table: main.*_temp

Question

I have a Xamarin.Forms app that uses a SQLite database locally on the device. Here's some sample data structure:

Table x: id, name

Table y: id, name

Table x_y: id, x_id, y_id

Since SQLite doesn't support altering columns, one of the schema updates we sent down in a patch did the following:

1. Rename table x to x_temp
2. Create new/updated table x
3. Insert all data from table x_temp into table x
4. Drop table if exists x

That seems to work just fine. However, when I'm attempting to run an insert statement on table x_y, I am getting a SQLite exception: "no such table: main.x_temp".

When I look at the SQLite query string while debugging there is no mention of table x_temp whatsoever. So, if I delete the entire database and re-create everything the insert works just fine.

I'm from a MSSQL background, am I not understanding something about SQLite in general? Is the foreign key constraint from table x_y trying to reference x_temp because I renamed the original table (I may have just answered my own question)? If that's the case, surely there is a way around this without having to cascade and re-create every table?

Any input would be appreciated. Thanks!

Answer 1

I believe that your issue may be related to the SQlite version in conjunction with whether or not Foreign Key Support has been turned on.

That is the likliehood is that :-

Is the foreign key constraint from table x_y trying to reference x_temp because I renamed the original table (I may have just answered my own question)?

Would be the issue, as you likely have Foreign Key Support turned on as per :-

• Prior to version 3.26.0 (2018-12-01), FOREIGN KEY references to a table that is renamed were only edited if the PRAGMA foreign_keys=ON, or in other words if foreign key constraints were begin enforced.

• With PRAGMA foreign_keys=OFF, FOREIGN KEY constraints would not be changed when the table that the foreign key referred to (the "parent table") was renamed.

• Beginning with version 3.26.0, FOREIGN KEY constraints are always converted when a table is renamed, unless the PRAGMA legacy_alter_table=ON setting is engaged. The following table summaries the difference:

• SQL As Understood By SQLite - ALTER TABLE

If that's the case, surely there is a way around this without having to cascade and re-create every table?

Yes, as the latest version of SQlite on Android is 3.19.0 (I believe), then you can turn Foreign Key support off using the foreign_keys pragma when renaming the table.

• Note Foreign Keys cannot be turned off within a transaction.

See SQL As Understood By SQLite - ALTER TABLE and PRAGMA foreign_keys = boolean;