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 https://github.com/techouse/mysql-to-sqlite3/blob/cdee3dad0c8f725820c3fd291397ad5da059e21e/src/mysql_to_sqlite3/transporter.py#L683 and then finally re-enabled once completed https://github.com/techouse/mysql-to-sqlite3/blob/cdee3dad0c8f725820c3fd291397ad5da059e21e/src/mysql_to_sqlite3/transporter.py#L750 so most of your troubles should have been handled already, but without any 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 https://github.com/techouse/mysql-to-sqlite3/pull/98? It includes table creation order computation with foreign key constraints. Could you please test if it works with your problematic database?

[techouse]

@langrock #98 was too complicated and didn't solve anything, because a topological sort is not a circular fix. The code only re-ordered acyclic dependencies. If the schema has a cycle, no ordering can make every FK valid at creation time. The circular schema tests still broke because recreating tables with their mutually dependent FKs in place is impossible in SQLite without special handling.

I'm now working on #101 which will add support for deferred foreign keys, but even that won't solve the problem of circular FKs.

I'll leave this issue open until #101 is ready to be merged and will then close it.

Feel free to play around with #101, if you have the time.