Sqlite error: No such table main.table_name exists

Question

In my UWP app, I am creating tables on the App Start in the sqlite db. Also, I have written various Alter commands wherein I check if the installed app contains the respective columns in Customertbl and rename the Customertbl table as Customertbl_old and create new table Customertbl and finally I store all rows from the Customertbl_old table into Customertbl table. And then I drop table Customertbl_old. Now, up to this part everything works correctly but, When I try to delete/ insert a row in OwnerTbl it throws an exception saying

Sqlite error: No such table main.Customertbl_old exists.

//**Code where a column name is updated by creating new table**
string tableCommand = "PRAGMA table_info(Recordings)"; 
SqliteCommand createTable = new SqliteCommand(tableCommand, db);
SqliteDataReader query = createTable.ExecuteReader(); 
tableCommand = "PRAGMA foreign_keys = off; " + " 
BEGIN TRANSACTION; " +
" ALTER TABLE Customertbl RENAME TO _Customertbl_old; " +
" CREATE TABLE Customertbl ( " + " ID INTEGER PRIMARY KEY AUTOINCREMENT, " + " CustomerName NVARCHAR(100) NULL, " + " Password NVARCHAR(100) NULL, " + " 
pkID INTEGER NULL, " + " ActivityName NVARCHAR(255) NULL);" +
 " INSERT INTO Customertbl(Name,Password) " + " SELECT Name,Password " + " 
FROM _Customertbl_old ;" + " COMMIT; " + " PRAGMA foreign_keys=on; " + " 
DROP TABLE 
_Customertbl_old";

// **code where exception occur**
using (SqliteConnection db = new 
SqliteConnection("Filename=" + App.dbName)) 
{ 
db.Open(); 
SqliteCommand deleteCommand = new SqliteCommand(); 
try 
{ 
    deleteCommand.Connection = db; 
    deleteCommand.CommandText = "DELETE FROM Ownertbl where fkId = @id";  
    deleteCommand.Parameters.AddWithValue("@id", id);  
    deleteCommand.ExecuteReader()//here the exception occur ;  
    db.Close(); 
 } 
}

Answer 1

TL;DR

If a previously-working use of ALTER TABLE... has broken due to upgrading SQLite to version 3.26 or later, a "quick-fix" is to use PRAGMA legacy_alter_table=ON before making those changes. However, it is probably better to re-organise the commands (see "The Safe Way"). If you are starting a new project with a post-3.26 version of SQLite, I would definitely recommend using the "The Safe Way" and avoiding the PRAGMA.

What Happened

Your problem arose because of changes made to SQLite's ALTER TABLE command in versions 3.25.0 (2018-09-15) and 3.26.0 (2018-12-01). For the official documentation, see ALTER TABLE on the SQLite website.

Prior to this version, renaming a table (e.g. from MY_TABLE to A_BETTER_NAMED_TABLE) only changed the name of the table. Any references to that table (i.e. in Foreign Key (FK) constraints or triggers) were not renamed. If the goal was to only rename the table, this could reasonably be regarded as a bug, since there is no (easy, official) way of altering those references, and you would be left with an inconsistent database.

The above changes "fixed" this issue by propagating changes to a table's name into any such references. You could thus rename a table that was referenced by FK constraints or triggers and be left with a consistent database.

When it Causes Problems

The OP's problem arises – indirectly – due to the very limited ability (when compared to a non-Lite database engine) of the ALTER TABLE command. In SQLite, all it can do is change the name of the table, the name of a column, or add new columns (to the "end" of a row). If you needed to do anything more complicated than that to a table, you had to get "inventive" (essentially, create a new replacement table and populate it from the old table). However, there are two ways of doing this, and one of them is "broken" by the above changes "trying to be more helpful".

The Safe Way

• Create a new (e.g. MY_TABLE_NEW) with all the new properties you need.

• Migrate the existing data from MY_TABLE to MY_TABLE_NEW (adding default/missing values as appropriate).

• Delete the original table MY_TABLE. At this point, any FK constraints and triggers that refer to MY_TABLE would fail, but that doesn't matter.

• Use ALTER TABLE MY_TABLE_NEW RENAME TO MY_TABLE to change the name of the new, correctly formatted table back to that of the original table. Any FK constraints/triggers references will now be satisfied again (assuming they are not to columns that were removed!)

The above will work both before and after the change. Before the change, no heed is paid to FK constraints/triggers; after the change, there will be no references to MY_TABLE_NEW so nothing will be propagated.

The Broken Way

Before the change, the following procedure would work equally well:

• Use ALTER TABLE MY_TABLE RENAME TO MY_TABLE_OLD. At this point, in the old version any FK constaints/triggers would be inconsistent, but that doesn't matter.

• Create a replacement table MY_TABLE with all the new properties you need. Any references will now be satisfied.

• Migrate the existing data from MY_TABLE_OLD to MY_TABLE (adding default/missing values as appropriate).

• Delete the original (but renamed) table MY_TABLE_OLD.

The problem arises with the new version because it will rename any references to the original table (MY_TABLE) so that they now reference the about-to-be-deleted table (MY_TABLE_OLD). None of the other steps will affect these references, so you are left with an inconsistent database (references to the non-existent MY_TABLE_OLD).

The Fix

Either change the order of the table-manipulation steps to those in The Safe Way or – as documented on the ALTER TABLE page – issue the command PRAGMA legacy_alter_table=ON before the ALTER TABLE command. This will prevent the "improved" behaviour from renaming references to MY_TABLE to MY_TABLE_OLD.