Issue with SQLite DROP TABLE statement

Question

EDIT: At this point, I found the errant typo that was responsible, and my question has become "How did the typo that I made cause the error that I received" and "How might I have better debugged this in the future?"

I've setup a database script for SQLite (through pysqlite) as follows:

DROP TABLE IF EXISTS LandTerritory;
CREATE TABLE LandTerritory (
  name   varchar(50) PRIMARY KEY NOT NULL UNIQUE, 
  hasSC  boolean NOT NULL DEFAULT 0
);

I'm expecting this to always run without error. However, if I run this script twice, (using the sqlite.Connection.executescript method) I get this error:
OperationalError:table LandTerritory already exists

Trying to debug this myself, I run DROP TABLE LandTerritory on its own and get: sqlite3.OperationalError: no such table: main.LandTerrito

I'm guessing this has something to do with the "main." part, but I'm not sure what.

EDIT: Okay PRAGMA foreign_keys=ON is definitely involved here, too. When I create my connection, I turned on foreign_keys. If I don't turn that on, I don't seem to get this error.

And I should have mentioned that there's more to the script, but I had assumed the error was occurring in these first 2 statements. The rest of the script just does the same, drop table, define table. A few of the tables have foreign key references to LandTerritory.

Is there a way to get something like line number information about the sqlite errors? That would be really helpful.

EDIT 2: Okay, here's another table in the script that references the first.

DROP TABLE IF EXISTS LandAdjacent;
CREATE TABLE LandAdjacent (
  tname1 varchar(50) NOT NULL,
  tname2 varchar(50) NOT NULL,
  PRIMARY KEY (tname1, tname2),
  /* Foreign keys */
  FOREIGN KEY (tname1)
    REFERENCES LandTerrito
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
  FOREIGN KEY (tname2)
    REFERENCES LandTerritory(name)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Looking at this, I found were the "LandTerrito" came from, somehow a few characters got cut off. I'm guessing fixing this may fix my problem.

But I'm really confused how a broken line in this table led to the script running correctly the first time, and then giving me an error related to a different table when I run it the second time, and how foreign keys played into this.

I guess, to reiterate from above, is there a better way to debug this sort of thing?

I know you made it clear how the script works, but just to be certain, can you post it, or at least the relevant parts? — , Aug 23 '12 at 21:15

yonojoy · Answer 1 · 2016-04-21T11:57:10.493

The source of the error is your typo

REFERENCES LandTerrito

in line 8 of your script. This leads to the "missing" table LandTerrito in the CREATE TABLE LandAdjacent statement.

If you run your two CREATE TABLE statements Sqlite wont complain. But if you have PRAGMA foreign_keys=ON; and try to run an INSERT or DELETE statement on the table LandAdjacent, you'll get the error no such table: main.LandTerrito.

Because of the foreign key constraints DROP TABLE on LandTerritory however will result in a DELETE on the table LandAdjacent, which triggers the error.

The following things will avoid the error

set PRAGMA foreign_keys=ON; before you drop the table (tested) or
add a dummy table LandTerrito (tested) or
drop LandAdjacent first, then LandTerritory (tested) or
dont use ON DELETE CASCADE (not tested)
and of course correcting the original typo.

score -2 · Answer 2 · answered Aug 23 '12 at 23:00

-2

Put a "GO" (or whatever equivalent is used in SQLlite) to terminate a batch between the drop table statement and the create statement

answered Aug 23 '12 at 23:00

John Bingham

1,996
1
12
15

Issue with SQLite DROP TABLE statement

2 Answers2