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;