SQLite Forum

Bug?: CREATE TABLE with unsatisfied FK and DROP TABLE results in Error
Login

Bug: CREATE TABLE with unsatisfied FK and DROP TABLE results in Error

(1) By Martin Rode (martinrode) on 2020-05-06 07:34:50 updated by 1.1 [source]

Hi,

we have noticed this bug during a create & purge of a much larger data model. I narrowed it down to the essential lines.

```
CREATE TABLE "column" (
  "id"              INTEGER PRIMARY KEY AUTOINCREMENT,

  "other_table_id"  INTEGER,
  "other_column_id" INTEGER,

  FOREIGN KEY (other_table_id) REFERENCES "table" (id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (other_column_id) REFERENCES "column" (id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE "column";
```

**Error: near line 12: no such table: main.table**

Removal of any column in the table fixes the Error (even the "id" column).


```
➜  sqlite-tools-osx-x86-3310100 git:(develop) ✗ ./sqlite3 --version                                                               
-- Loading resources from /Users/martin/.sqliterc
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837bb4d6
```

Bug?: CREATE TABLE with unsatisfied FK and DROP TABLE results in Error

(1.1) By Martin Rode (martinrode) on 2020-05-07 05:21:49 edited from 1.0 [link] [source]

Hi,

we have noticed this bug during a create & purge of a much larger data model. I narrowed it down to the essential lines.

CREATE TABLE "column" (
  "id"              INTEGER PRIMARY KEY AUTOINCREMENT,

  "other_table_id"  INTEGER,
  "other_column_id" INTEGER,

  FOREIGN KEY (other_table_id) REFERENCES "table" (id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (other_column_id) REFERENCES "column" (id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE "column";

Error: near line 12: no such table: main.table

Removal of any column in the table fixes the Error.

➜  sqlite-tools-osx-x86-3310100 git:(develop) ✗ ./sqlite3 --version                                                               
-- Loading resources from /Users/martin/.sqliterc
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837bb4d6

(2) By Kees Nuyt (knu) on 2020-05-07 14:56:52 in reply to 1.1 [link] [source]

The error message is generated because somewhere else you issued :

PRAGMA foreign_keys=on;
, possibly by setting SQLITE_DEFAULT_FOREIGN_KEYS=1 during compilation or by including it in ~/.sqliterc .

What you see here is that the existence of the parent table "table" is not checked during CREATE TABLE, but is properly checked during any ather action, like INSERT or DROP. This behavior is likely necessary to allow tables referencing each other in both ways, or to allow self-referencing tables.

In some cases, foreign key checks can be postponed until COMMIT. Note the remark on PRAGMA defer_foreign_keys.

-- 
Regards,
Kees Nuyt

(3) By Martin Rode (martinrode) on 2020-05-07 16:34:44 in reply to 2 [link] [source]

Thanks Kees for the reply. PRAGMA foreign_keys are on, that is intended.

The problem here is, that I cannot DROP TABLEs which are empty. It does not happen in all circumstances, only in certain combinations of table and columns and foreign keys. So, sometimes you can DROP a table referencing, sometimes not.

Look at this complete example, starting with an empty DB.

➜  sqlite-tools-osx-x86-3310100 cat script.sql
PRAGMA foreign_keys;

CREATE TABLE "table" (
  "id"              INTEGER PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "column" (
  "id"              INTEGER PRIMARY KEY AUTOINCREMENT,

  "other_table_id"  INTEGER,
  "other_column_id" INTEGER,

  FOREIGN KEY (other_table_id) REFERENCES "table" (id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (other_column_id) REFERENCES "column" (id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE "table";
DROP TABLE "column";
➜  sqlite-tools-osx-x86-3310100 rm test.db && ./sqlite3 test.db < script.sql
foreign_keys
------------
1           
Error: near line 18: no such table: main.table
➜  sqlite-tools-osx-x86-3310100 

(4) By Martin Rode (martinrode) on 2020-05-12 11:22:52 in reply to 1.1 [link] [source]

I like to give a little more information here.

Again the complete script which FAILS (and it should not):

➜  sqlite-tools-osx-x86-3310100 cat script.sql                              
PRAGMA foreign_keys;

CREATE TABLE "table" (
  "id"              INTEGER PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "column" (
  "id"              INTEGER PRIMARY KEY AUTOINCREMENT,

  "other_table_id"  INTEGER,
  "other_column_id" INTEGER,

  FOREIGN KEY (other_table_id) REFERENCES "table" (id) ON DELETE CASCADE ON UPDATE CASCADE
  ,FOREIGN KEY (other_column_id) REFERENCES "column" (id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE "table";
DROP TABLE "column";
➜  sqlite-tools-osx-x86-3310100 rm test.db && ./sqlite3 test.db < script.sql
foreign_keys
------------
1           
Error: near line 18: no such table: main.table

The FK to "table" is in the way when dropping "column".

If we change the script to not include the self-referencing foreign key, DROPPING the tables work. So I am very sure that this behaviour is a bug.

So here, we have SUCCESS, script does not fail.

➜  sqlite-tools-osx-x86-3310100 rm test.db && ./sqlite3 test.db < script.sql
foreign_keys
------------
1           
➜  sqlite-tools-osx-x86-3310100 cat script.sql
PRAGMA foreign_keys;

CREATE TABLE "table" (
  "id"              INTEGER PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "column" (
  "id"              INTEGER PRIMARY KEY AUTOINCREMENT,

  "other_table_id"  INTEGER,
  "other_column_id" INTEGER,

  FOREIGN KEY (other_table_id) REFERENCES "table" (id) ON DELETE CASCADE ON UPDATE CASCADE
  -- ,FOREIGN KEY (other_column_id) REFERENCES "column" (id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE "table";
DROP TABLE "column";
➜  sqlite-tools-osx-x86-3310100 rm test.db && ./sqlite3 test.db < script.sql
foreign_keys
------------
1           

The FK to "table" is not in the way when dropping "column".

(5) By Gunter Hick (gunter_hick) on 2020-05-12 12:55:25 in reply to 4 [link] [source]

Can you drop the "column" table first? Do you have circular FK references?

If you are taking measures to prevent orphaned records between tabes, surely you can exercise the same caution regarding the tables.

(6) By Martin Rode (martinrode) on 2020-06-15 19:10:42 in reply to 5 [link] [source]

There is no records. This is just an empty schema. There are NO CIRCULAR REFERENCES (except for the one self-ref). The script I am showing is the ENTIRE sqlite database. Just copy & paste it and see for yourself.