Thanks Keith. LEGACY_ALTER_TABLE=ON lets me change the table name without error. But the docs say "New applications should leave this flag turned off."
Is there any other way of checking if the schema is invalid besides attempting to change the name of a table? I went through the pragmas and commands below and they did not report that the schema was invalid. SQLite version 3.29.0 2019-04-27 20:30:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t1(f1); sqlite> create view v1 as select * from t1; sqlite> drop table t1; sqlite> create table t2(f1); sqlite> .schema CREATE VIEW v1 as select * from t1; CREATE TABLE t2(f1); sqlite> pragma integrity_check; ok sqlite> pragma quick_check; ok sqlite> pragma optimize; sqlite> vacuum; sqlite> pragma foreign_key_check; sqlite> pragma database_list; 0|main| 1|temp| sqlite> .lint Usage lint sub-command ?switches...? Where sub-commands are: fkey-indexes sqlite> .lint fkey-indexes sqlite> alter table t2 rename to t20; Error: error in view v1: no such table: main.t1 ------------------- Date: Fri, 03 May 2019 13:27:18 -0600 From: "Keith Medcalf" <kmedc...@dessus.com> To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Error when renaming a table when an invalid view exists in the schema Message-ID: <802bb8bcf08af448bd5d9a4b9bf2f...@mail.dessus.com> Content-Type: text/plain; charset="utf-8" Use PRAGMA LEGACY_ALTER_TABLE=ON; The "Legacy alter table" does not require the database to be "valid/consistent" after executing the "alter table" command. The non-legacy (default) mode requires that the database be "transformed" in to a "valid/consistent" state in order for the alter table command to be processed. A side effect of this is that if the database was invalid BEFORE you issue the alter table command, and it is still invalid after, that in the "validity" checking mode (the default), the alter table c ommand will not be processed (since it will throw an error that the transformation did not result in a "valid" schema). Correctly, you should either (a) drop view v1, or (b) create table t1 so that the database schema is consistent before you attempt to use ALTER TABLE. (Or, if you know that your schema is invalid, you can turn off validity checking with pragma LEGACY_ALTER_TABLE). Since creating a view is nothing more than storing a statement in the database, it is not checked when you create a view or drop a tab le that the schema is still valid (otherwise you would get a message of the form "Cannot drop table t1 because it is referenced in a view" or mayhaps view v1 would be dropped automatically). Perhaps a setting "ALLOW_INVALID_SCHEMA" needs to be added such that after each DDL statement the schema is checked for validity and if it is not valid then the DDL is tossed with an error (such as oc curs in SQLFat databases)? ... _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users