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 the left side of the expression will be returned regardless of whether or not there is a match on the join. We purposefully put our referencing column on the left.
Then we joined it to the table we want to reference with the JOIN....ON statement so there is a relationship between the tables.
We then excluded all instances where there was a match with the WHERE clause by only selecting instances of the join where the referenced column was null (if it wasn't null then the values in the 2 tables did match and therefore, was not breaking the constraint).
Why this is important
Sure you got your data loaded by turning off the foreign key check so you're good right? No you need to take this step and update all the values that don't match because
1. you added the constraint for a reason right (probably consistent, reliable data) so you want to make sure that your data is consistent and reliable.
2. If you issue an alter table statement on a table that is violating a foreign key constraint the server will error and you won't be able to alter your table with out setting the foreign_key_checks = 0 again.
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 the left side of the expression will be returned regardless of whether or not there is a match on the join. We purposefully put our referencing column on the left.
Then we joined it to the table we want to reference with the JOIN....ON statement so there is a relationship between the tables.
We then excluded all instances where there was a match with the WHERE clause by only selecting instances of the join where the referenced column was null (if it wasn't null then the values in the 2 tables did match and therefore, was not breaking the constraint).
Why this is important
Sure you got your data loaded by turning off the foreign key check so you're good right? No you need to take this step and update all the values that don't match because
1. you added the constraint for a reason right (probably consistent, reliable data) so you want to make sure that your data is consistent and reliable.
2. If you issue an alter table statement on a table that is violating a foreign key constraint the server will error and you won't be able to alter your table with out setting the foreign_key_checks = 0 again.
Comments