MYSQL: error: cannot add or update child row: foreign key constraint is not satisfied

Question

Using MySQL on Windows and getting an error when trying 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 an error present in the subject line (cannot add or update the child line: foreign key failed):

 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 might cause the error. I also have a foreign key referencing td_domain_name.domain_id, but I don’t think this should interfere ...

Also, as a workaround (unsuccessful) to this problem (which I am sure can be easily resolved), I tried just doing

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

; however, the number of entries does not match as it should (i.e., the left outer join will not be successful).

Appreciate it!

Answer 1

You have td_domain_name.domain_name values ​​that do not have corresponding tf_traffic_stats.domain_name values. You must fix this before adding a foreign key constraint.

You can find the problematic lines 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 are many reasons that foreign keys can produce this error. Unfortunately, MySQL is not very specific for what reason.

It looks like you have the corresponding indexes and your column types are the same, so I assume that you have data in the second table that cannot be mapped to the first. Something like the following query will show you any lines from td_domain_name that don't have the 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 a foreign key constraint, you will need to get rid of entries in td_domain_name that cannot be matched with tf_traffic_stats , or create the corresponding entries in tf_traffic_stats .

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

Answer 3

You can get more information on why the query does not work with SHOW INNODB STATUS . Buried at the exit is a section called LAST FOREIGN KEY ERROR , which will explain in more detail why the key fails.

If both of these tables have data in them, then most likely there is one or more values ​​in the td_domain_name table that are not present in the tf_traffic_stats table, which causes the creation of FK to fail. If you absolutely need to create an FK without correcting incorrectly matched keys, you can temporarily disable FK checks with set foreign_key_checks=0 , and then execute the alter statement.