Can not drop a table...
(1) By Mark Lawrence (mark) on 2021-01-04 11:55:48 [link] [source]
I have a database, which appears to be ok:
sqlite> pragma integrity_check; integrity_check --------------- ok
pragma foreign_key_check also returns no errors, yet I am unable to drop a table due to some schema issue:
sqlite> drop table countries; Error: no such table: main.Service_Catalog
I'll be able to trace the various schema statements to work out what my issue is so I'm not asking for help. But perhaps there is a different pragma that reports on such conditions, and in more detail?
I don't know how I got the current schema (lots of fast-paced development) but it would also be nice if SQLite prevented me from creating it.
 Unfortunately I am unable to share the schema publically.
(2.1) By Richard Hipp (drh) on 2021-01-04 18:07:50 edited from 2.0 in reply to 1 [link] [source]
If you do:
sqlite3 YOURDB .schema | sqlite3
Do you get errors coming from the second "sqlite3" command?
(3.1) By Simon Slavin (slavin) on 2021-01-04 13:26:18 edited from 3.0 in reply to 1 [link] [source]
I'm trying to figure out whether your table definitions or your data are at fault.
Use the .schema command in the command line tool to dump the schema. Find the two tables
- Does that database actually use FOREIGN KEYs anywhere ?
- Are both those tables shown in the schema ?
- Does either of the two tables use a FOREIGN KEY to the other table ?
(4) By Mark Lawrence (mark) on 2021-01-04 14:50:28 in reply to 2.0 [link] [source]
I don't see what you mean by second "sqlite3" command. What you wrote appears to be a 3rd argument to the original "sqlite3" command(?) which results in this as expected:
Error: near "sqlite3": syntax error
I've managed a minimal test case though:
DROP TABLE IF EXISTS a; CREATE TABLE a(ID INTEGER NOT NULL PRIMARY KEY); DROP TABLE IF EXISTS b; CREATE TABLE b( ID INTEGER NOT NULL PRIMARY KEY, c_ID INTEGER REFERENCES c(ID), a_ID INTEGER NOT NULL REFERENCES a(ID) ON DELETE CASCADE ); DROP TABLE a;
The above produces the following:
Error: near line 11: no such table: main.c
If you remove the "ON DELETE CASCADE" then the error goes away.
(5) By Stephan Beal (stephan) on 2021-01-04 14:53:02 in reply to 4 [link] [source]
I don't see what you mean by second "sqlite3" command.
His example was mangled by markdown. There's a pipe symbol before the second sqlite3 command. Tap the 'source' link at the top of his response to see what he intended.
(6) By Gunter Hick (gunter_hick) on 2021-01-04 15:02:47 in reply to 4 [link] [source]
Your sequence of foreign key dependencies is corrupt. You need to create table c before you create table b. Dropping table a, which is referenced by ON DELETE CASCADE, implies deleting all rows of the referencing table b, which has unresolved foreign key restraints due to table c not yet existing. Always create your object (referenced) tables before creating your relation (referencing) tables.
(7.2) By Mark Lawrence (mark) on 2021-01-04 15:46:14 edited from 7.1 in reply to 6 [link] [source]
I'm aware of what the situation is, and following the FK dependencies eventually sorted that out. But having lots of references makes that a very manual task because SQLite provides no context in its error message.
My original comment can be enhanced to be more direct:
Can the error reporting be made clearer? I.e. point to the table that has the dangling reference, not just to the missing target table.
Could the "integrity_check" or "foreign_key_check" pragmas pick up and report on this type of broken schema?
[Comment edited to add this 3rd item] Perhaps SQLite could even set an internal flag whenever a DDL command is issued during a transaction, and check for an invalid schema just before COMMIT? It would be nice not to put a database into this situation at all.
[Edited a 2nd time to say] Sometimes you can't avoid the creation or deletion of tables in the "wrong" order - I regularly make use of circular references, and make schema changes to those tables, so one or the other of CREATE TABLE or DROP TABLE is potentially going to have this issue.
(8.1) By Donald Griggs (dfgriggs) on 2021-01-04 23:11:46 edited from 8.0 in reply to 4 [link] [source]Deleted
(9) By Warren Young (wyoung) on 2021-01-04 23:30:24 in reply to 7.2 [source]
I think you'll find
PRAGMA defer_foreign_keys = true helpful.
(10) By Mark Lawrence (mark) on 2021-01-05 15:36:27 in reply to 9 [link] [source]
That is a helpful pragma for circular references, but it doesn't change the behaviour around this particular issue (at least not with my testing).
(11) By Klaas van Buiten (KlaasVanBuiten) on 2021-01-05 16:09:32 in reply to 10 [link] [source]
What is the output of following query?
sqlite> select sql from sqlite_master where tbl_name in ('countries', 'Service_Catalog');