Considering the following reduced program:

/* I aim to misbehave */
PRAGMA foreign_keys = ON;

select sqlite_version();

create table a
(
  a_id integer primary key
);

create table b
(
  b_id integer primary key
);

create table c
(
  c_id integer primary key,
  a_id integer references a(a_id) on delete cascade,
  b_id integer references b(b_id) on delete cascade
);

/* note, there is no data in a, b, or c */

select 'dropping a';
drop table a;

select 'dropping b';
drop table b; /* this is line 29 */

It produces the following output:

3.31.1
dropping a
dropping b
Error: near line 29: no such table: main.a

This is all a bit astonishing.

Plainly the code is behaving badly. Table c should have been dropped before a or b but nevermind that. Let's look at that error.

First we note that drop a works but then drop b doesn't work? But what FK enforcement could c possibly have to do given on delete cascade and this is a drop operation? Why is c even looking at the a_id FK? Given that it is, we could probably give a little bit more help in the error message so that a person could have some clue that the problem is with c and not a.

Of course this is easy enough to spot in this example but it's less obvious if you have a few hundred tables and the diagnostic doesn't mention where the problem lies.

I found two good ways to debug this:

• breakpoints in the name lookup code
• the notification APIs

both gave the correct c name as an interim step which made it easy to spot what had gone wrong.

FWIW, this was first observed on 3.28 so it's not a regression or anything. And also SQLite's diagnostics seem to be at least as good as anyone else's so this is not a case of "you guys are much worse than MySQL" or any such thing. But MySQL does fail the drop a which I think is better than failing drop b.

The drop is definitely wrong with FK constraints on. But actually both drops are equally wrong and the first one doesn't generate an error at all.

Still we can aspire to great error messages.

Maybe "drop failed due to FK from main.c to main.a"

"drop b" generating "main.a not found" is surprising especially after "drop a" succeeded...

Anyway, fodder for the future.

"drop b" generating "main.a not found" is surprising especially after "drop a" succeeded...

Actually, it is not surprising at all. You dropped table main.a and then it was not found when it was looked for.

You can always turn off foreign_key enforcement if you wish to transition the schema through a referentially invalid state.

Sorry I'm just not being specific enough here. What I found surprising was that drop b failed after drop a had succeeded. I expected both to fail. This isn't an ongoing problem, my code was broken and I fixed it. But I noticed this weird thing where the 2nd drop fails and not the first hence the report.

Summary, given this errant code:

• drop a should have failed
• diagnostics explaining why it failed would be helpful when debugging broken SQL

To figure this all out I just read the "drop means delete all first" code which isn't that complicated.

I believe it's always a good idea to look how other, even more standards compliant database systems behave in the same situation.

PostgreSQL shows this error:

ERROR:  2BP01: cannot drop table a because other objects depend on it
DETAIL:  constraint c_a_id_fkey on table c depends on table a
TIP:  Use DROP ... CASCADE to drop the dependent objects too.

SQLite should show something similar in case of foreign keys turned on. It's not ok to limit Foreign Keys to refusing DML operations.

This diagnostic would have made it a lot easier to spot my bug :D