Foreign Key and Index creation in referential order using JDBC
Foreign Key and Index creation in referential order using JDBC
I am trying to write a java application which copies tables to another schema.
If I simply use the result from show create table foo_bar in the target schema, I get errors if referenced tables for foreign keys are not created yet. So I build my own scripts using DatabaseMetaData [getTables()] to create the tables, without the foreign keys. After creating all the tables, I will add the FKs. And this is where trouble begins.
show create table foo_bar
DatabaseMetaData
Since I can not (and do not want to) manually sort these FK scripts, I'm looking for a way to run these in order, depending on the references they have.
Is there a way to generate these scripts in referential order, maybe a third party java library buried deep in the internet? Or is there no escape from running all these alter table scripts one by one, until program receives no errors? What is the best approach to this problem?
I am using jdbc to connect mysql using innodb storage engine. If answer contains comparison information about other rdbms as well, much appreciated.
jdbc
mysql
innodb storage engine
SET UNIQUE_CHECKS=0
SET FOREIGN_KEY_CHECKS=0
@marekful Thanks for the comment but I'm not having problems when inserting data. Problem is, my application can not alter the tables in correct order every time, to add foreign keys. Looking for a solution to create the foreign keys in correct order.
– sadeceengin
Jul 2 at 14:12
Do a mysqldump of the tables into 'file.sql' from source. On target, do
myqsl ..... < file.sql. All will be fine. Also, if you want to create just the table structures (with all indexes/keys), do the same but pass --nod-data to mysqldump.– marekful
Jul 2 at 14:14
myqsl ..... < file.sql
--nod-data
mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0. This avoids problems with tables having to be reloaded in a particular order and This enables you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html– marekful
Jul 2 at 14:30
mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0. This avoids problems with tables having to be reloaded in a particular order
This enables you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys.
@marekful Well, I wasn't planning to use dump files. I prefer building and running queries within the java application (don't ask why, I might add extra columns at some point) but since you mentioned I will give it a try to see the results.
– sadeceengin
Jul 2 at 14:48
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
There is
SET UNIQUE_CHECKS=0andSET FOREIGN_KEY_CHECKS=0. If you know the source database is consistent, do this when inserting into the target. You can also use mysqldump which can (or will by default) add these statements into its output.– marekful
Jul 2 at 14:05