Try it again with pragma legacy_alter_table, and see what happens.

That doc was likely written before better tracking of dependencies between objects,
and restoring the old behavior for your upgrade might help. --DD

PS: I'll defer to the experts for what to do w/o that pragma.

You might also be interested on this post https://sqlite.org/forum/forumpost/1d904235f5

The change you mention would be easily done via that writable schema way.

Thanks for the suggestion! Using the legacy_alter_table pragma fixes the issue, but ideally I'd want a solution that doesn't require me to enable pragmas the documentation discourages me from enabling. So I'll wait for what the experts have to say then ;)

It's also interesting (at least to me) that this only appears to be broken with views - creating an index or a trigger on the affected table does not prevent the migration procedure from succeeding.

Once you drop the table users you have an "inconsistent schema". This prevents the ALTER TABLE command from functioning because it is designed to transmorgify the schema from one valid state to another valid state by applying the changes specified in the command. If either the before or after states are invalid, then the command cannot be executed (will fail with an error).

If you are knowingly and with aforethought transitioning from an invalid state to another state which may or may not be invalid, then you need to turn off the consistency checking by using the legacy_alter_table pragma which exists, solely for the purpose, of allowing you to knowingly use ALTER TABLE with an invalid starting or ending state: this also happens to be how the ALTER TABLE command worked before it was changed to work properly -- thus the name "legacy".

After you drop the table users you must also drop any views which refer to that table. Indexes and triggers that refer to the table are dropped automatically. views cannot be dropped automatically because they are not compiled statements stored in the internal dictionary. They are merely textual substitutions.

If you fail to make the schema consistent, then you will only be able to modify it with legacy_alter_table enabled.

ideally I'd want a solution that doesn't require me to enable pragmas

Delete views. Follow steps. Recreate views.

I will note, however, that the very same procedure that is used to detect that the "starting state" is invalid could be used to get rid of the view when the table is dropped.

I am quite sure, however, that this would result in quite the volume of complaints that "I used to have this 47 page long view that used another 47 page long view that used another 47 page long view, each of which used another smattering of 47 page views. I dropped a table and the whole kaboodle of views was deleted!"

It could also be used to prevent the dropping of the table which I am sure would generate just as many complaints.

So the solution there would be to add yet another pragma to determine whether to "cascade table drops though views" or not. Which would yet surely generate quite the volume of complaints.

In each and all of the above cases the answer is simply that the "changer of the schema" should know what it is that it is doing, why, and the implications of their actions.

Having the software ask "Are you Sure?" over and over again when presented with a command to carry out is folly. If you are not sure then you would not have given the command you did, would you not have?

this would result in quite the volume of complaints that

Why? Dropping dependent views is typically opt-in and explicit, with the CASCADE keyword required.

Case in point in PostgreSQL. No need for a pragma or an are-you-sure question.

Personally I'd vote for better ALTER TABLE support instead :)

Because the OP's issue is entirely due to it, since the documented work-around no longer works.
Only proper ALTER TABLE can atomically edit a table schema w/o affecting dependent objects,
or affecting only the subset that depend on what is being edited.

This is one of the last remaining weak point of SQLite, I think.

Along the years, I've seen SQLite gain so many features, some very large, so there's still hope :).

There's a long tradition on the ML/forum of Xmas wishes, this is mine :).

PS: My other wish is basically built-in TOAST in SQLite, with efficient subsetting like PostgreSQL does,
PS: i.e. substr(blob, off, len) can read only the necessary shard(s) in O(1) instead of O(N)
PS: once the row has been located (in O(log(N)), w/o having to follow a long linked-list of overflow pages.
PS: Which would also avoid rewriting the whole row and its huge blob(s) when tiny values of that row change.

That is a proprietary extension. I assume (although it is not stated) that the default in PostgreSQL is RESTRICT. SQLite3 currently defaults to the IGNORE option.

SQLite3 currently defaults to the IGNORE option.

There's no such option that I can see in the doc for DROP TABLE.

IF EXISTS is something else, and explicitly opt-in.

Furthermore, that doc mentions the implicit cascade to indexes and triggers,
but does not say anything about dependent objects, i.e. views or triggers (on other tables) using the dropped table.

That doc perhaps needs a refresh?

Of course not. That is why it is invisible and the current default.

Dependent objects are not modified:

sqlite> create table x(x);
sqlite> create table y(x);
sqlite> create trigger x_bi before insert on x begin insert into y(x) values (new.x); end;
sqlite> .schema
CREATE TABLE x(x);
CREATE TABLE y(x);
CREATE TRIGGER x_bi before insert on x begin insert into y(x) values (new.x); end;
sqlite> drop table y;
sqlite> .schema
CREATE TABLE x(x);
CREATE TRIGGER x_bi before insert on x begin insert into y(x) values (new.x); end;
sqlite> create table y(x);
sqlite> drop table x;
sqlite> .schema
CREATE TABLE y(x);
sqlite>

nor are dependent referrents:

sqlite> create table x(x integer primary key);
sqlite> create table y(x integer references x);
sqlite> .schema
CREATE TABLE x(x integer primary key);
CREATE TABLE y(x integer references x);
sqlite> drop table x;
sqlite> .schema
CREATE TABLE y(x integer references x);
sqlite> create table x(x integer primary key);
sqlite> .schema
CREATE TABLE y(x integer references x);
CREATE TABLE x(x integer primary key);
sqlite> drop table y;
sqlite> .schema
CREATE TABLE x(x integer primary key);
sqlite>

nor views either

sqlite> create table x(x);
sqlite> create view y as select x from x;
sqlite> .schema
CREATE TABLE x(x);
CREATE VIEW y as select x from x
/* y(x) */;
sqlite> drop table x;
sqlite> .schema
CREATE VIEW y as select x from x;
sqlite>

I'm considering using "legacy_alter_table", but would still try to avoid it if possible and have been wondering how step 3 in the 12-step migration can be done.

The documentation states "Remember the format of all indexes, triggers, and views associated with table X.", but I don't see how to find all the views associated with table X, as the suggested query ("SELECT type, sql FROM sqlite_schema WHERE tbl_name='X'") doesn't seem to return views created as "CREATE VIEW Y AS SELECT * FROM X". What am I missing?

(Edited to change a clearly false assertion to what is true.)

... What am I missing?

A couple of facts: (1) The sqlite_schema table has no columns with which to discover tables involved in a view¹; and (2) Its "name" column for a view named "Y" will be "Y", not "X".

You would have to parse the "sql" column content for views which involve "X".

1. ^{^} The "tbl_name" column only names a table covered by an index when the type is 'index' and is otherwise adds no information.

A couple of facts: (1) The sqlite_schema table has no column named "tbl_name"; and (2) Its "name" column for a view named "Y" will be "Y", not "X".

I copied the example straight from the documentation and it does work for me. I was using X to get all the objects related to table X:

SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a ←[1mtransient in-memory database←[0m.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>: create table x(x);
sqlite>: create index foo on x(x);
sqlite>: SELECT name,tbl_name,type,sql FROM sqlite_schema WHERE tbl_name='x';
x|x|table|CREATE TABLE x(x)
foo|x|index|CREATE INDEX foo on x(x)

You would have to parse the "sql" column content for views which involve "X".

That's what I suspected and just wanted to confirm, as the documentation gave me the impression that there is a way to track the dependencies without parsing. Thank you.

(1) The sqlite_schema table has no column named "tbl_name";

Uh?

sqlite> create table t(v);
sqlite> select * from sqlite_schema;
┌───────┬──────┬──────────┬──────────┬───────────────────┐
│ type  │ name │ tbl_name │ rootpage │        sql        │
├───────┼──────┼──────────┼──────────┼───────────────────┤
│ table │ t    │ t        │ 2        │ CREATE TABLE t(v) │
└───────┴──────┴──────────┴──────────┴───────────────────┘

"Uh?" indeed. Even "Duh". That started as a longer sentence regarding absence of a means to enumerate participating tables in sqlite_schema. But it's certainly nonsense now, soon to be edited.