Hi, I am following these steps in https://sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes

to make some schema changes, but looks like there's some issue with it, I'm wondering if it's a designed feature or if it's a routine that could be improved.

To make the reproduce steps simple, consider a database has this schema, that is two tables "Test", "New_Test", and a view "TestView":

CREATE TABLE Test ( id INTEGER PRIMARY KEY ) ; CREATE TABLE New_Test ( id INTEGER PRIMARY KEY ) ; CREATE VIEW TestView AS SELECT Test.id FROM Test ;

Then if I do

DROP TABLE Test; ALTER TABLE New_Test RENAME TO Test;

I will get an error:

error in view TestView: no such table: main.Test

I mean of course there's no such table "main.Test", because I dropped it, and I want another table to be the "main.Test".

I know that if I drop the view "TestView" before the "ALTER TABLE..." step, then recreate the view after the "ALTER TABLE..." step will work, but it just feel weird.

So like I mentioned before, is this a designed feature or is this a routine that could be improved?

The error you see occurs because, for several versions now, SQLite has, upon table renames, undertaken to revise references to the renamed table within views.

You might have hoped that the rename which is failing, because it cures a view's reference to a non-existent table, would thereby succeed. Apparently, the code lacks that foresight.

You can do things in a different order, possibly with an intermediate rename, to get around this. Or, more simply,

PRAGMA legacy_alter_table = TRUE;

will suspend the check which your rename caused to be failed, as described here.

Thank you for the reply. I am aware the solutions exist. My intention is moving forward and probably help SQLite be a better product by pointing out potential issues or by giving some suggestions.

So that makes PRAGMA legacy_alter_table = TRUE; not an option for my intention, since with time goes on, software’s modern behavior will finally replace the legacy one. I think the intermediate rename one will do the trick, and it’s nice idea to solve the issue, thank you for pointing this out, I didn’t see that before.

So if SQLite now behaves like that intentionally, at least part of the 12 steps for altering table in the document I mentioned before is incorrect. The ‘Drop View’ operation in step 9 should probably move up to before step 7 the ALTER TABLE RENAME TO operation, since in step 7, the error I saw will probably occurs.

Well, it looked like either a bug report or a variety of puzzlement.

Regarding "with time goes on, software's modern behavior will finally replace the legacy one", I think that legacy behavior has another 3 decades to go, based on claims made at sqlite.org and observed practices relating to backward compatibility.

I have seen that the SQLite dev team is quite open to consideration of improvements. If you want to help in that regard, you would do better to suggest specific improvements, giving compatibility issues their proper due.

From how the ALTER TABLE change was explained and discussed in this forum, I can tell you that what it does is quite intentional. If you have seen a doc error, you can do the SQLite user community a favor by specifically identifying it.

Regarding "with time goes on, software's modern behavior will finally replace the legacy one", I think that legacy behavior has another 3 decades to go, based on claims made at sqlite.org and observed practices relating to backward compatibility.

Well, there are many reasons why the legacy behaviour is still useful for many purposes and will continue to be, even though the new behaviour is also useful for many purposes too.

I think your comments make sense. Maybe I will make my purpose more clearly next time :) I treated the behavior of legacy alter table as deprecated somehow, but base on the fact that SQLite is doing not bad in the backward compatibility area, maybe I can treat the behavior of legacy alter table as a good friend to the modern one in future.

Well, there is actually a quite simply way of thinking about pragma legacy_alter_table.

The original (or legacy_alter_table=1) method did not assume or require that the database schema is internally consistent before making a change, and did not require it to be consistent after the change was made. It simply processes alter table commands verbatim, with no attempt to maintain consistency or coherence or integrity of the database.

The new (or legacy_alter_table=0) method assumes and (sometimes) requires that the database schema is internally consistent before making a change; and requires that the database schema is internally consistent after having made the change. It does this my making all the necessary changes to the schema to transform it from the before the command is executed assumed consistent state to the after the command is executed consistent state, such as renaming columns/indexes/constraints/views/triggers as necessary wherever they may be found (and if something is being missed that is a bug and should be reported so Richard can find and fix it). If the database schema is inconsistent before the change or would be inconsistent after the change, then an error is thrown. (though not all inconsistencies before the change are errors).

Note that this sometimes precludes being able to use a legacy_alter_table=0 alter table command to change the database from an inconsistent state to a consistent one. In such a case one must use legacy_alter_table=1.

Or for short, legacy_alter_table=1 means do as you are told, only what you are told, exactly as you are told; whereas legacy_alter_table=0 applies the transformation requested on an assumed consistent schema and results in a consistent schema, so its use on an inconsistent schema may be an error.

This is a clear and convincing explanation for legacy_alter_table=on/off to me. 👍