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





There is SET UNIQUE_CHECKS=0 and SET 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



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.

-

Popular posts from this blog

api-platform.com Unable to generate an IRI for the item of type

api-platform.com Unable to generate an IRI for the item of type I'm trying to setup my first api-platform instance and have run into some presumably noob questions around IDE. What i'm trying to do is setting up an register and reset entity in order to deal with registrations and password reminders from an app. And then have the entity's passed to a custom controller to do some checks, balances and mails afterwards (haven’t gotten around to that yet). But I keep running into this error when posting to my custom entities: { "@context": "/api/contexts/Error", "@type": "hydra:Error", "hydra:title": "An error occurred", "hydra:description": "Unable to generate an IRI for the item of type "AppXXXUserBundleEntityRegister"", "trace": [ { "namespace": "", "short_class": "", "c
Read more

How to set up datasource with Spring for HikariCP?

How to set up datasource with Spring for HikariCP? Hi I'm trying to use HikariCP with Spring for connection pool. I'm using jdbcTempLate and JdbcdaoSupport. This is my spring configuration file for datasource: <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"> <property name="dataSourceClassName" value="oracle.jdbc.driver.OracleDriver"/> <property name="dataSource.url" value="jdbc:oracle:thin:@localhost:1521:XE"/> <property name="dataSource.user" value="username"/> <property name="dataSource.password" value="password"/> </bean> But unfortunately the following error message is generating: Cannot resolve reference to bean 'dataSource' while setting bean property 'dataSource'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource'
Read more

Display dokan vendor name on Woocommerce single product pages

Display dokan vendor name on Woocommerce single product pages With woocommerce I am using Dokan plugin and I am trying to display the vendor name, rating and vendor location on single product pages. I tried this code to display vendor name but no luck: //show store name add_action( 'woocommerce_single_product_summary', 'show_store_name', 20 ); function show_store_name() { printf( '<b>Seller Name:</b> <a href="%s">%s</a>', dokan_get_store_url( $author->ID ), $store_info['store_name'] ); } Any help is appreciated. 1 Answer 1 I don't use dokan plugin. Here is the way to get the (post) author ID and to make your code work: add_action( 'woocommerce_single_product_summary', 'display_author_name', 20 ); function display_author_name() { // Get the author ID (the vendor ID) $vendor_id = get_post_field( &#
Read more