3

i don't have a big knowledge in database so excuse me if there is bad syntax or bad description but i did my best :)

When i importing to child table it ignores foreign key though the parent table is empty and hasn't any data yet, i used the following command to dump the data without this parent table data

mysqldump --ignore-table --no-create-info old_database > data.sql

then i importing this data to new database I've created

mysql new_database < data.sql

then i dumped the parent row data only using this command

mysqldump --tab="\\uploads" 

importing parent_data as below

mysqlimport "\\uploads\parent_data.txt" new_database

why MySQL accepts this and ignore the existing of foreign key ? and is there a way to force MySQL to not ignore FKs when importing ?

child table show create info

| child | 
CREATE TABLE `child` 
(
    `id` bigint(19) NOT NULL,
    `flag` int(10) NOT NULL,
    PRIMARY KEY (`id`, `flag`),
    KEY `FK72610D66B7AE7814` (`flag`),
    CONSTRAINT `FK72610D66AFB765B8` 
        FOREIGN KEY (`id`) REFERENCES `parent` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

parent create info

| parent | 
CREATE TABLE `parent` 
(
    `id` bigint(19) NOT NULL AUTO_INCREMENT, 
    ...
) ;

1 Answer 1

3

By default mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0.

So If you want force MySQL to not ignore FKs when importing, Use --compact switch in your command:

For Example:

mysqldump --ignore-table=example.test --compact --no-create-info old_database > data.sql

Refrence: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_compact

Update

By defult, foreign_key_checks is set 1. this means if each of your DML queriies has FK error, the query execution will be rejected with the error.

For incresing the speed of restores, MySQL set foreign_key_checks 0 (in Dump file) and after that the restoring was done, MySQL set foreign_key_checks 1. unless you had set foreign_key_checks 0.

2
  • MySQL won't stop surprising me... the option to not ignore FK is to dump a compact version... For me, the safe thing would be to never ignore any constraint; and allow for some flags to change this behaviour if the need for speed is critical.
    – joanolo
    Commented Apr 26, 2017 at 7:07
  • @joanolo look at the latest update of my post.
    – HamoonDBA
    Commented Apr 26, 2017 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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