1

I have a mysql 5.6 schema with about 60 tables. I need to write some code (not sure if it will be Sql or java) to populate the data. The schema has lots of FK etc.

Is there some good way (I am thinking some tool, script...) I could use that will look at the metadata of the DDL and tell me in which order should I insert data in the tables so all FK are met?

I obviously want to avoid trial and error... The output of such script/tool would be for example: 1. insert into country/continent tables 2. insert person 3. insert ocupation/cv

3
  • That would be pretty tough to pull off magically. What if a references b, b references c, and c references a? What if the chain of references is 162 tables long? Oct 30, 2014 at 1:27
  • that is what I am asking, if there is some existing tool/script that does that. In such cases you mention it could at least point out the issue, etc. Oct 30, 2014 at 8:18
  • There are tools such as Red-Gate's Dependency Tracker (not free) which might help you. But usually you do not have a lot of tables in a single update, so you usually can figure it out quickly. If you are doing BULK loads of data, you might choose to disable the constraints, load the data, correct any violations, then reestablish the constraints.
    – RLF
    Oct 30, 2014 at 14:26

1 Answer 1

2

If you are sure enough that all constraints would be met..You can think off turning off the foreign key checks and after making sure that all constraints are ok, you can turn on foreign key checks.
You can write a script to verify that if all constraints are ok.

use following to turn off foreign key checks

set foreign_key_checks=0

use following to turn on checks again after necessary verification

set foreign_key_checks=1

Hope it helps

Not the answer you're looking for? Browse other questions tagged or ask your own question.