2

I have:

  • a CSV file (5000+ records);
  • an empty item table;
  • am empty favourite table;
  • a number of other empty tables.

I would like to populate the item table with the contents of the CSV file. Then I would like to create some foreign key relationships between both tables.

In what order do I do this?

I keep receiving an error every time I attempt this:

#1452 - Cannot add or update a child row: a foreign key constraint fails...

Note that my empty favourite table already has some foreign key constraints set up. I am using PhpMyAdmin and MySQL Workbench.

Am I supposed to have all the tables populated with data before I set up foreign key relationships? Or do I set up the relationships then populate with data?

7

You have several choices. You can either create the tables without the constraints & add them afterwards, or create the tables with the foreign keys & them import the data with foreign key checks disabled - simply run SET FOREIGN_KEY_CHECKS=0; in your mysql session to temporarily disable them.

For example:

mysql> insert into favourite values( 1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tmp`.`favourite`, CONSTRAINT `favourite_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`))
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into favourite values( 1,1);
Query OK, 1 row affected (0.00 sec)
  • 1
    Thanks @Philᵀᴹ - I read on a few other discussions it's not advisable to turn off foreign key checks as it can lead to data inconsistencies? – jonboy Mar 14 '17 at 16:28
  • 1
    @johnny_s Yeah, it can. Easy to write some validation SQL scripts though. – Philᵀᴹ Mar 14 '17 at 16:29
1
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> LOAD DATA INFILE ...
mysql> LOAD DATA INFILE ...
mysql> SET FOREIGN_KEY_CHECKS=1;
  • 1
    Thanks @Jim - can you explain what's going on in your answer? – jonboy Mar 14 '17 at 16:29
  • Disabling foreign key checks for the session, loading the data, and re-enabling foreign key checks. That should prevent FK errors as you load the data. – user118129 Mar 14 '17 at 18:44
0

If you're using terminal, login to MySQL using your credentials with the following command:

mysql -u[your_username] -p[your_password]

Set your MySQL foreign key constraint checks to 0 using the next command:

SET FOREIGN_KEY_CHECKS = 0;

Import your database using a source command like the one below:

SOURCE /path/to/your/sql/file.sql

And set your MySQL foreign key constraint checks back to 1 using the command that follows:

SET FOREIGN_KEY_CHECKS = 1;

P.S. Tested on Ubuntu 14.04, 16.04, 18.04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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