Mysql ignore foreign key errors

Question

I have a BASH program that inserts into my DB somes lines. Unfortunately, when MYSQL encounter an error(especially foreign key constraint errors), the entire SQL request stops.

Isn't there a way to skip the lines where the foreign key constraint doesn't apply, and keep adding the others?

    insertVtigerInfos() {
$mysql -h $db_address -u $db_user -p$db_passwd $db_name -e "delete from $db_vtiger_name;load data local infile '$vtiger_temporary_file' REPLACE INTO TABLE $db_vtiger_name fields terminated by ';'"
}


    ----------------------dÃ©marrage---------------------------------
Tue May  6 16:03:13 CEST 2014
ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (`RH2QUALIF`.`info_vtiger`, CONSTRAINT `info_vtiger_ibfk_1` FOREIGN KEY (`nom_caisse`) REFERENCES `definition_ip_switch` (`nom_caisse`) ON DELETE CASCADE ON UPDATE CASCADE)
-------------------------fin------------------------------------
Tue May  6 16:03:14 CEST 2014

Updating thread with sample : Table toward whom the foreign key constraint applies :

+-------------+------------+
| nom_caisse  | quat_octet |
+-------------+------------+
| BCP         | NULL       |
| CEA         | NULL       |

Table having the foreign key constraint :

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| code_site       | varchar(32) | NO   | PRI | NULL    |       |
| nom_caisse      | varchar(32) | NO   | MUL | NULL    |       |
| ip_routeur      | varchar(32) | NO   |     | NULL    |       |

Data trying to be inserted :

131001M0;CEA;<ip>
131001G0;CEALS;<ip>

When mysql tries to insert the rows, the 1st is OK, the 2nd is not (because CEALS is not in the table which the foreign key is. But mysql doesn't add ANY row because he found one not matching... I just want it to add the 1st row...

Try SET FOREIGN_KEY_CHECKS = 0; but after execution of your script set it to 1 — M Khalid Junaid
– M Khalid Junaid, Commented May 6, 2014 at 14:15
This will add all the lines, even the ones that don't match. I just want mysql to keep continuing after finding foreign key errors — Gui O
– Gui O, Commented May 6, 2014 at 14:17
As per docs Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks = 0 will not be verified for consistency — M Khalid Junaid
– M Khalid Junaid, Commented May 6, 2014 at 14:22
The --force option don't care about foreign key errors. It still stops — Gui O
– Gui O, Commented May 7, 2014 at 6:00

Gui O · Accepted Answer · 2014-05-07 06:38:05Z

1

The only solution i found is to do that :

$mysql -f -h $db_address -u $db_user -p$db_passwd $db_name -e "SET foreign_key_checks=0;delete from $db_vtiger_name;load data local infile '$vtiger_temporary_file' REPLACE INTO TABLE $db_vtiger_name fields terminated by ';';SET foreign_key_checks=1;"

and then sending another request which delete the rows according to the foreign key.

delete from info_vtiger where NOT EXISTS ( select nom_caisse from definition_ip_switch where definition_ip_switch.nom_caisse=info_vtiger.nom_caisse ) ;

Unfortunately, the foreign key constraint is no more useful in this case. posting it as a solution, if anyone have some other ways to do it...

answered May 7, 2014 at 6:38

Gui O

3834 gold badges10 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zafar Malik · Accepted Answer · 2014-05-06 14:58:49Z

0

If your requirement is just to insert new records but not replace to existing records then you can use as per below:

$mysql -h $db_address -u $db_user -p$db_passwd $db_name -e "delete from $db_vtiger_name;load data local infile '$vtiger_temporary_file' INSERT IGNORE INTO TABLE $db_vtiger_name fields terminated by ';'"

answered May 6, 2014 at 14:58

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

4 Comments

Gui O Over a year ago

Mysql send me an error on this insert ignore : ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT IGNORE INTO TABLE info_vtiger fields terminated by ';'' at line 1

Zafar Malik Over a year ago

@Gui O: First use '$vtiger_temporary_file' as $vtiger_temporary_file without enclosing it in single quote, if still get error then share this file sample data at least one row data.

Gui O Over a year ago

Another solution i can use is to set the foreign_key_checks=0, insert then put it back to value 1. Then sending another request as : delete from info_vtiger where nom_caisse NOT IN defintion_ip_switch ??

Zafar Malik Over a year ago

set foreign_key_checks=0 means you are allowing rows in child table even does not exist in its parent table means you are making your data inconsistant..but if you are sure that your data is correct and there is no need to further check this data then you can do it...further now your error message is related with syntax error related with load infile not foreign key so if you share the details then your problem can be sorted out.

Collectives™ on Stack Overflow

Mysql ignore foreign key errors

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related