Arminius Arminius - 1 year ago 45
MySQL Question

Establishing correct order importing data to prevent FK conflicts

All data is in CSV format
Let's say I have two tables: customers and their addresses. Naturally, I have set an FK in addresses table named customer_id which references customer_id that is the PK in that parent table, customers.

When they are empty I have no problem in filling them with data in a form, as I pick the ID from customers and pass it to the child table. However I am at phpmyadmin now and I want to import them both with their data.

I am thinking about different scenarios.

1) Creating the structure of the tables but still empty and then

- import the customers table first
- import addresses table afterwards
- and then implement the FK

but I fear I will get the : cannot add or delete child row foreign key constraint fails

2) Create the structure of the tables empty and then

- do it the other way around, starting first with addresses, then customers

3) Importing both tables with their data (in csv) and then try to link them through the FK


Does anyone experienced know how to confront this?

Answer Source

Well, the first way is the way to go. You fear that you get a FK constraint fail...sure, this can happen. But then your data is corrupt and that's what foreign keys are protecting you from. That's what they are made for. So I don't understand why are afraid of it.

Your second scenario...sure, you can do that, but then you might as well omit the foreign key and you can't be sure, that each child has an actual parent. When you try to add the foreign key afterwards, you will get the foreign key constraint fails, too.

Your third scenario...when your data is properly normalized, there's no way to link them afterwards.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download