Praying and Running

When you use LOAD DATA LOCAL INFILE on a table that contains a foreign key constraint you get an error with an explanation that a the table cannot be updated because a foreign key constraint fails..... 1. Turn off foreign key checks with the following command at the MySQL command line client a. set foreign_key_checks = 0; 3. issue your load data command again 4. once the data is loaded issue the following query select referencing_table . referencing_column FROM referencing_table LEFT JOIN referenced_table ON ( referencing_table . referencing_column = referenced_table.referenced_column ) WHERE referenced_table.referenced_column IS NULL; This will give you a list of all the column values in the offending table that do not match a value in the referenced table. 5. Update all of the values listed in the query results from above 6. turn the foreign_key_checks back on with the following a. set foreign_key_checks = 1; Why this works We are doing a LEFT JOIN which means that everything on t...