Export table order

[SedesGobhani]

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 anyone could suggest. TIA.

[pawelsalawa]

This issue used to appear from time to time, but I always had problem to reproduce it, because it happened to work correctly for me each time.

Are you perhaps willing to share your database schema (with or without data)? That way I could maybe reproduce the problem and fix it.

[pawelsalawa]

The thing is that SQLiteStudio does add foreign key pragmas at the beginning and the end of exported SQL file, so it imports without problems. How come it did not do it for you? What SQLiteStudio version are you using?

Here's how it exports for me:

--
-- File generated with SQLiteStudio v3.3.3 on czw. gru 16 11:15:33 2021
--
-- Text encoding used: System
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;
....
...
...
COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

[SedesGobhani]

I have 3.3.3. Interestingly, if I run the export, those pragma commands DO show up in the output SQL file. However, when I try to import that same SQL file into a newly created database (using Execute SQL from file menu command) I get "[12:52:47] Could not execute SQL due to error." with error "no such table...". If I then open SQL editor window and enter the command "PRAGMA foreign_keys = off;", then reattempt the very same operation, I get "[12:55:31] Finished executing 498 queries in 0.039 seconds." For some reason the pragmas work when executed from the SQL editor, but are ignored in the SQL file.

[pawelsalawa]

This is a very interesting observation (that it doesn't work from "Execute SQL from file"). I created #4343 to track this.

[pawelsalawa]

Also created #4344 for the checkbox option that you mentioned.

[SedesGobhani]

Thank you.