MySQL Bugs: #1451: Export Table Creation

Bug #1451	Export Table Creation - Error with Foreign Key
Submitted:	30 Sep 2003 16:24	Modified:	3 Oct 2003 1:20
Reporter:	Andrés Montañez	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.13	OS:
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
When Exporting a group of Tables with the MySQL-Front 2.5 or with the option Show Create with MySQL Control Center, the Tables with Foreign Keys(FK), the FK are in the CREATE TABLE query. The Problem comes when I execute the file, it will create a table with FK to a table that doesn't exist.

How to repeat:
Posible Export I:

CREATE TABLE child (Code tinyint(4) NOT NULL, Fcode tinyint(4) NOT NULL, PRIMARY KEY (Code, Fcode), KEY NewIndex (Fcode), CONSTRAINT 0_92 FOREIGN KEY (Fcode) REFERENCES father (Code)) TYPE=InnoDB;

CREATE TABLE father (Code tinyint(4) NOT NULL, PRIMARY KEY  (Code)) TYPE=InnoDB;

When executing the Query, will be a problem, because the referenced table doesn't exist.

Posible Export II:

If I only select several tables, not all tables, I will have the same error of Part I, and a integrity break.

Suggested fix:
The best soultion that I can see, is to Generate the Foreign Keys AFTER the structure of the tables, and ONLY if the REFERENCED TABLE exists, with an ALTER TABLE query.

Example:

CREATE TABLE child (Code tinyint(4) NOT NULL, Fcode tinyint(4) NOT NULL, PRIMARY KEY (Code, Fcode), KEY NewIndex (Fcode)) TYPE=InnoDB;

CREATE TABLE father (Code tinyint(4) NOT NULL, PRIMARY KEY  (Code)) TYPE=InnoDB;

ALTER TABLE child ADD FORIGN KEY (Fcode) REFERENCES father (code);

Create first all the table structures and then create the Foreign Keys, only if the referenced table exists.

This is a documented issue on InnoDB at section FOREIGN KEY constraints.
I am assign Heikki for further comments if necessary:
Below the text regarding that:

------------------------------------------------------------------------------
If you want to import several dumps of tables, but the dumps are not correctly ordered for foreign keys, starting from 3.23.52 and 4.0.3 you can turn the foreign key checks off for a while in the import session:

SET FOREIGN_KEY_CHECKS=0;

This allows you to import the tables in any order, and also speeds up the import.
------------------------------------------------------------------------------

Hi!

Mysql-Front was written by Ansgar Becker. We cannot fix problems with it.

Generally, the setting

SET FOREIGN_KEY_CHECKS=0;

is the way to import tables dumps.

Best regards,

Heikki