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, 
    ...
) ;
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.

  • 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 Apr 26 '17 at 7:07
  • @joanolo look at the latest update of my post. – HamoonDBA Apr 26 '17 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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