FOREIGN Key problems

[langrock]

Hi,

Thanks for putting together this tool for the general public. I have been trying it out on a few databases that contain tables with foreign-key constraints. This always failed unless suppressing the transfer of foreign keys using the -X option. Not the end of the world, but requires quite a bit of manual labor to restore the constraints afterwards. Could this possibly be due to the order with which the tool parses the various tables in the database? Maybe I simply don't understand how to properly use it or am missing a flag that would help avoid this problem.

Thanks

[techouse]

Hey there,

To start, it would be great if you could provide some specific details.

The issue with SQLite, unlike MySQL, is that all foreign key constraints must be added to the tables at their creation time. In contrast, MySQL allows these constraints to be added after the tables have been created, so the order in which tables are created does matter.

Currently, the tables are just queried using the following MySQL query:

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = SCHEMA()

and looped through. While the tables are created, SQLite foreign key checks are disabled

mysql-to-sqlite3/src/mysql_to_sqlite3/transporter.py

Line 683 in cdee3da

self._sqlite_cur.execute("PRAGMA foreign_keys=OFF")

and then finally re-enabled once completed

mysql-to-sqlite3/src/mysql_to_sqlite3/transporter.py

Line 750 in cdee3da

self._sqlite_cur.execute("PRAGMA foreign_keys=ON")

so most of your troubles should have been handled already, but without andy specifics around your case I can only guess.

Calculating a vector that would support arbitrary foreign key constraint creation would be quite complex, and I haven’t even attempted it.

TL;DR: SQLite doesn’t support ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY

[langrock]

Thanks so much for taking the time to explain the origins of this problem. I didn't realize that SQLite3 doesn't have that capability at the moment. I have been using SQLPro for SQLite on a Mac and the app allows for the creation of foreign keys after the fact, likely doing all the work behind the scenes to create a new table with foreign key constraints and then copying all the data over before dropping the original table. Learning something new every day ...

[techouse]

@langrock, would you be kind enough to check out PR #98? It includes table creation order computation with foreign key constraints. Could you please test if it works with your problematic database?