Praying and Running

You bring data into a referenced column through LOAD DATA LOCAL INFILE but when you try to insert data into the referencing column you get the "Cannot add or update a foreign key constraint fails...." error. You have looked at the referenced column and the referencing data again and again and you don't see the problem. If you open up Query Browser and look at the referenced data you may find paragraph marks. This problem confounded me for quite some time. I understood that the paragraph marks were causing the problem but I didn't understand how they were getting there. This is a classic case where "RTFM" applies. MySQL by default understands line termination by the return character "\r". The Windows OS by default writes line termination as a carriage return and a new line "\r\n". The extra \n is causing the problem. How to deal with this is right in the MySQL manual. But I thought I would post it because I do and did "RTFM" and...

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...