Skip to main content

Find unmatched referenced column values

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.

Comments

Popular posts from this blog

2013 BeeBumble 10k: Race Report

This has quickly become one of my favorite races of the year and I am not alone because there was a record 575 participants across the different events this year. I like the small town atmosphere, I like the course that is an out and back on a country road, and the timing makes it a perfect tune up race for the fall season. After my performance here the last two years I am also really beginning to appreciate it for the confidence booster it is and it's ability to add a breath of fresh air to the end of the training cycle for the 2013 Indianapolis Monumental Marathon. For this race I added an early morning shake out run to get my body primed for running. This was just a short and easy 20 minute run very first thing out of bed. Nearly all of my running is first thing in the morning but  I've never done it as a shakeout for a race. I liked it and think I'll keep it. The last 5k I ran I didn't do a warm up and I learned my lesson because it cost me a very slow fi...

My AMDG Run: An Answered Prayer.

I have never been a person to pray for specific favors. It never felt right to me. My prayers generally are for strength, guidance, an acceptance of what comes my way. That is always how I "felt" right praying. To pray for a specific favor like a good grade, to do well in some competition and things like that just always felt a little wrong and to pray for physical healing was something I never would have considered. I am not saying this was any kind of selflessness. I am not sure exactly what it was. A fear of seeming childish or perhaps it was out of fear that my faith would be shaken if the prayer wasn't answered. Regardless, this is how I generally pray. And that is not to say that I don't or didn't think that God doesn't concretely answer prayers. I believe that He does. For instance I have always prayed when overwhelmed something like "God, I cannot do this all alone, please help me" and I will somehow, someway find my way through whatever it...

2015 Valpo Half Marathon Race Report

This was my big tune-up race for the Indianapolis Monumental Marathon. I always run a half-marathon at this point in the build up to the Monumental to get a final big fitness boost, a reality check on where I am at fitness-wise and, if all goes well, probably the most important aspect is the confidence boost that I get. I got one heck of a confidence boost yesterday, 10/25/2015, at the Valpohalf Half-marathon in Valparaiso IN. Valparaiso is about 2 hours from home which is kind of right there on the line of driving on race morning or staying in a hotel the night before. This time we decided to get up and drive. Valparaiso is on central time which puts it an hour behind us. Meaning the 8:30 AM start was really a 9:30 AM start for me.  Making the decision to drive that much easier. I have been dealing with some issues on the top of my right foot, which is probably extensor tendinitis, for the last couple of weeks. I saw my soft-tissue guy last Friday. He worked on it some and got...