Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
That's great , i've never considered that point , thanks ! –  warl0ck Aug 25 '11 at 13:34
    
This is a good answer. After all, notice that the beginning of mysqldumps start off with disabling foreign key checks. That is why mysqldumps can be reloaded in alphabetical order for each database regardless of referential integrity. +1 !!! –  RolandoMySQLDBA Aug 25 '11 at 13:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.