MySQL – Foreign Keys and Data Import Best Practices

foreign keyMySQLphpmyadmin

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?

Best Answer

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)
Related Question