MySQL ‘LOAD DATA INFILE’ : Can’t replace entry when foreign key exists

innodbMySQLPHP

Here's a minimal case for my database scheme:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Well , And what we have inside:

    mysql> select * from test;
+-----+-------+
| id  | name  |
+-----+-------+
| 111 | AAABA |
+-----+-------+
1 row in set (0.00 sec)

mysql> select * from test2;
+-----+
| id  |
+-----+
| 111 |
+-----+
1 row in set (0.00 sec)

Now i need to read from an external csv file , with duplicate primary key , into the first table:

mysql> LOAD data infile '/tmp/tar.csv' REPLACE INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`eduman`.`test2`, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`))

And the contents for /tmp/tar.csv:

%> cat /tmp/tar.csv 
111,AAA

As i know from MySQL , it probably does a 'delete' then 'insert' , which caused the problem , it can't be deleted as the foreign key exists.
So how can i force an update to be executed , instead of a 'delete then insert' ?

Appreciate any of your responses.

From internet , i found some workable way , which suppress the checks temporarily , but is there any better response , it looks like an dirty hack , and i'm not sure if this could be a problem in multi-user environment , e.g another table with foreign key constraints may be damaged.

SET FOREIGN_KEY_CHECKS = 0;
Blabla ...
SET FOREIGN_KEY_CHECKS = 1;

Best Answer

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

The above link clearly explains that mysqldump too puts set foreign_key_checks = 0; in the dump file so that data can be restored without the foreign key constraint error. I have always used set foreign_key_checks = 0 for importing data into tables and then set it back to 1.

Related Question