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

Running Goals or Running Goal; What is it That I Want?

The other day I got all caught up in setting running goals for myself for the rest of the year. After the goals were set, I found myself starting to worry about how I was going to reach them. Wondering if there would be time to sufficiently prepare for them all. My goals were to run a 5k in under 20 minutes, to run a half marathon in 1:25 minutes and qualify for and run in the Boston marathon in 2014. At first, I thought that setting and meeting these goals would make me a more serious runner. In fact, I thought that to be the "serious" runner I want to be that I needed these goals. However, while I was running the other morning I realized that first of all, I was thinking about how to shift my training to meet my 5k goal. Then I began thinking about the right approach to a 1:25 half while training to qualify for Boston in a full marathon a month later. That is when I realized that the ancillary goals were starting to consume my focus and quite possibly i...

The Rosary: Staying Focused

There are several obstacles that someone who has committed to pray the Rosary will face. Since the Rosary is a long prayer one of the biggest challenges I face is staying focused. If you have read my other posts or if you are just familiar with the prayer, you know that the mental contemplation of the Mysteries is the main focus of the prayer. So getting distracted really detracts from the prayer. Even if I am only praying 1 chaplet of the Rosary, it still takes about 20 minutes. In that amount of time your mind is bound to wander some. I have found several different things to help me when my mind begins to drift off to the worries of everyday life. The first is to understand that this is going to happen and be watching for it. If I am not vigilant I can get through a whole decade and realize I have not thought one single time about the Mystery that I should have been contemplating. So before I begin the prayer I make a conscious determination to be watchful of my wandering mind. O...

2019 Indianapolis Monumental Marathon Race Report

On November 9th, 2019 I ran the Indianapolis Monumental Marathon (IMM). The short report is that I whined a lot during the build up and incessantly complained that I had gotten fat and slow but on race day, a semblance of my old self showed up and ran 1 hell of a race crushing my stated goal of 2:57 by nearly 4 minutes. The long report follows. I really had kind of given up on ever running a fast marathon again. After my back issues last year I just never got going full steam again. Instead I had gotten fat and slow because I let my diet slide and put on about 15 pounds and I was trying to run without fully committing to the work of marathon training. This summer I had decided to just maybe, kind of run the IMM for fun and not really lose my summer to training for it. But then Chad, a friend of mine who runs with a group on Sundays that I sometimes go to, asked my if I wanted to do my long runs for IMM together. I reluctantly agreed. Chad and I put up a couple of solid long...