-1

I have a relational SQLite database with tables accessing each other via foreign keys. When I use SqlLiteStudio "Export" to output to a new SQL file, it writes out SQL "CREATE" and "INSERT INTO" statements based on alphabetical order of table names. When I then import the same SQL file into a new database (which requires use of the command "Execute SQL from File", because the import command seems to only import csv into a table), I get the "no such table" error, because the line to create the table being referenced is not yet read. So if I edit the SQL file to put the tables at the top, then I get the "FOREIGN KEY constraint failed" error. So then if I edit the SQL file to all the tables insert operations below the table creations, AND reorder the inserts by table so that no table precedes its table dependencies, only then I can successfully import the SQL file, reproducing the original SQLite database file. Seems like a bug in SqlLiteStudio. I suppose worse case I can write a post-processing script, but I'm hoping to find out if there's some better method people are using. I just prefer to save in text format for backup into an existing git repo, and also make it easier for db migration later. TIA.

Mark Seagoe
  • 480
  • 8
  • 15

1 Answers1

1

You could turn Foreign Key Support Off before the import and then back On after the import using

PRAGMA foreign_keys = off;
....
PRAGMA foreign_keys = on;

see https://sqlite.org/pragma.html#pragma_foreign_keys

MikeT
  • 51,415
  • 16
  • 49
  • 68
  • That worked. For the record, I created new empty database, and with it selected, opened SQL editor, typed in the keys off command here and hit F9 (execute query), then did the import as I mentioned in the original post, it imported with no errors(!!!), then I ran the keys on command. So it's an SQLite thing, whereas I had thought it was a SQLiteStudio thing, so that explains why I didn't see it in their documentation. Thanks! – Mark Seagoe Dec 15 '21 at 17:37
  • @MarkSeagoe by default SQLite has Foreign Keys off, but most of the SQLite tools, in including studio turn it on. – MikeT Dec 15 '21 at 18:49
  • SQLiteStudio does add these 2 pragmas to the exported SQL file automatically. – Googie Dec 16 '21 at 10:16
  • @Googie it didn't work, at least for me... https://github.com/pawelsalawa/sqlitestudio/issues/4343 – Mark Seagoe Dec 20 '21 at 05:43
  • Looks like it's a bug as it is getting fixed now. – Mark Seagoe Dec 24 '21 at 22:17