Mysql – Why MySQL ignores foreign keys when importing

foreign keyimportMySQL

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

Best Answer

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.

Related Solutions

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Mysql – XML dump of MySQL database don’t include foreign key constraints. What can be the reason? sqldump(.sql) file list it

This is a bug in mysqldump that Oracle has characterized as a "feature request."

http://bugs.mysql.com/bug.php?id=66821

In normal mode (not XML), mysqldump just uses the server's output from SHOW CREATE TABLE tablename.

if (!opt_xml ...
    ...
    my_snprintf(buff, sizeof(buff), "show create table %s", result_table);

When running in --xml mode, mysqldump uses the output from SHOW KEYS FROM 'tablename', which doesn't include the foreign key constraints.

my_snprintf(buff, sizeof(buff), "show keys from %s", result_table);

Since the two different operating modes of mysqldump fetch the data from the server in such different ways, so the addition of this information to an XML dump is not exactly a trivial change.

There is not a simple workaround, although as noted in the bug report, the information is available in information_schema and you can access this from mysqldump if you supply the --skip-lock-tables option (locking information_schema isn't allowed). You'll have to take the data you retrieve, parse and extract the relevant bits, and reformat it as needed before it would be all that useful.

Best Answer

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

Related Solutions

Mysql – Finding rows for a specified date range

Mysql – XML dump of MySQL database don’t include foreign key constraints. What can be the reason? sqldump(.sql) file list it

Related Question