Issues creating tables containing FOREIGN KEYS

vwbusguy / sqlite-to-mysql

Script to convert and add sqlite3 database into a mysql/mariadb database

MIT License

108 stars 34 forks source link

Issues creating tables containing FOREIGN KEYS #1

Open devincowan opened 7 years ago

devincowan commented 7 years ago

@vwbusguy, I really appreciate this script! Do you have a solution for cases where the sqlite tables contain foreign keys? Seems that this results in ERROR 1005 when importing to SQL...

vwbusguy commented 6 years ago

@devincowan, I haven't seen that here. Do you happen to have a safe schema dump of an sqlite db as an example?

devincowan commented 6 years ago

@vwbusguy: It is a mySQL database that I'm attempting to import into. Would love to get you the sqlite dump, but afraid it has clinical data in it that can't be shared.

I will try to replicate the issue on a non-sensitive dump and send that your way. Thanks!

edugonza commented 6 years ago

Hi I have a similar problem due to the fact that MariaDB does not accept the REFERENCES syntax to define FOREIGN KEYS.

vwbusguy commented 3 years ago

It looks like technically, MariaDB accepts REFERENCES, but doesn't actually do anything with it. https://mariadb.com/kb/en/foreign-keys/#syntax

This is technically the design of MariaDB, but that doesn't mean we can't come up with a workaround. If someone can provide a test sqlite dump, I can try to translate the REFERENCES into a CONSTRAINT.