MYSQL: Error: Cannot add or update a child row: a foreign key constraint fails

Question

Hi all,

Using MySQL on Windows OS, and am getting an error upon attempting to create a foreign key between two tables:

CREATE TABLE tf_traffic_stats  ( 
domain_name     char(100) NOT NULL,
session_count   int(11) NULL,
search_count    int(11) NULL,
click_count     int(11) NULL,
revenue         float NULL,
rpm             float NULL,
cpc             float NULL,
traffic_date    date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY(domain_name,traffic_date))

and

CREATE TABLE td_domain_name  ( 
domain_id   int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
domain_name char(100) NOT NULL,
update_date date NOT NULL,
PRIMARY KEY(domain_id))

The following statement gives me the error present in the subject line (cannot add or update a child row: a foreign key constraint fails):

ALTER TABLE td_domain_name
ADD CONSTRAINT FK_domain_name
FOREIGN KEY(domain_name)
REFERENCES tf_traffic_stats(domain_name)
ON DELETE RESTRICT 
     ON UPDATE RESTRICT

Can someone point me in the right direction of what may be causing the error. I also have a foreign key referencing td_domain_name.domain_id, but I don't think this should be interfering...

Also, as a workaround (failed) to this issue (which I'm sure can be easily resolved), I've tried simply performing a

td_domain_name left outer join tf_traffic_stats on td_domain_name.domain_name=tf_traffic_stats.domain_name

; however, the number of records do not match up as they should (i.e., left outer join not successful).

Appreciate it!

Answer 1

You have td_domain_name.domain_name values that don't have matching tf_traffic_stats.domain_name values. You must fix this before adding the foreign key constraint.

You can find the problematic rows using the following query:

SELECT td_domain_name.domain_name
FROM td_domain_name LEFT JOIN tf_traffic_stats
  ON td_domain_name.domain_name = tf_traffic_stats.domain_name
WHERE tf_traffic_stats.domain_name IS NULL

Answer 2

There's a lot of reasons that foreign keys can produce that error. Unfortunately, MySQL isn't very specific about which reason it is.

It looks like you've got the proper indexes, and your column types match, so my guess is that you have some data in the second table that can't be correlated to the first one. Something like the following query will show you any rows from td_domain_name that don't have a corresponding domain name in tf_traffic_stats:

SELECT * FROM td_domain_name WHERE domain_name NOT IN 
    (SELECT DISTINCT(domain_name) FROM tf_traffic_stats);

Before you can create the foreign key restraint, you'll have to get rid of the entries in td_domain_name that can't be matched to tf_traffic_stats, or else create corresponding entries in tf_traffic_stats.

Just as an aside, it sounds like from your comments that td_domain_name is a static list of domains. I'm going to make a wild guess based on your table names that you might want the foreign key the other way around, ie. ensuring that every domain in tf_traffic_stats exists in td_domain_name?

Answer 3

You can get more details as to exactly why the query is failing with SHOW INNODB STATUS. Buried in the output is a section called LAST FOREIGN KEY ERROR, which will explain in greater detail why the key's failing.

If both of those tables have data in them, then most likely there's one more or more values in the td_domain_name table that aren't present in the tf_traffic_stats table, causing the FK creation to fail. If you absolutely have to have the FK created without fixing the mis-matched keys, you can temporarily disable the FK checks with set foreign_key_checks=0, then do the alter statement.