MySQL Bugs: #11121: Foreign key constraints referencing a table that has not yet been created

Bug #11121	Foreign key constraints referencing a table that has not yet been created
Submitted:	6 Jun 2005 20:58	Modified:	13 Jun 2005 19:23
Reporter:	Al Willingham	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.7 Beta	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When migrating from Oracle 9.2 to MySQL 4.1.12 using the Migration Toolkit,  any constraint that references as a foreign key, a table that is created later in the script, is not created. No errors are generated during the migration. 

The command line tool will generate "can't create table "x"  an error number 150 is included.

Also, there are other tables that are successfully created but the foreigh keys are not generated. Not errors are created anywhere. Table structures are the same for those that create errors and those that don't.

CREATE TABLE `wrlscarr`.`networktype2components` (
  `networkcompid` BIGINT(10) NOT NULL,
  `networkid` BIGINT(10) NULL,
  `comments` VARCHAR(255) NULL,
  `nettype2compid` BIGINT(10) NOT NULL,
  PRIMARY KEY (`networkcompid`),
  CONSTRAINT `fk_net2comp` FOREIGN KEY `fk_net2comp` (`networkid`)
    REFERENCES `wrlscarr`.`operator2network` (`networkid`)

)
ENGINE = INNODB;

CREATE TABLE `wrlscarr`.`operator2network` (
  `operatorid` BIGINT(10) NOT NULL,
  `networkid` BIGINT(10) NOT NULL,
  `networktypeid` BIGINT(10) NOT NULL,
  `expectedlaunchdate` DATETIME NULL,
  `comments` VARCHAR(255) NULL,
  `networktechnology` BIGINT(10) NOT NULL,
  `populationcoverage` BIGINT(10) NULL,
  `populationcoverageasofdate` DATETIME NULL,
  `networkcapacity` BIGINT(10) NULL,
  `networkcapacityasofdate` DATETIME NULL,
  `networksubscribers` BIGINT(10) NULL,
  `actuallaunchdate` DATETIME NULL,
  `networksubscribersasofdate` DATETIME NULL,
  `frequencyid` BIGINT(10) NOT NULL,
  `populationcoveragesource` VARCHAR(25) NULL,
  `networkcapacitysource` VARCHAR(25) NULL,
  `networksubscriberssource` VARCHAR(25) NULL,
  `expectedlaunchdatetype` INTEGER(2) NULL,
  `actuallaunchdatetype` INTEGER(2) NULL,
  `populationcoverageasofdatetype` INTEGER(2) NULL,
  `networkcapacityasofdatetype` INTEGER(2) NULL,
  `networksubscribersasofdatetype` INTEGER(2) NULL,
  PRIMARY KEY (`operatorid`),
  CONSTRAINT `fk_o2n_operator` FOREIGN KEY `fk_o2n_operator` (`operatorid`)
    REFERENCES `wrlscarr`.`operator` (`operatorid`)

)
ENGINE = INNODB;

How to repeat:
Create a script with two tables with the first table referencing the second as a foreign key.

Suggested fix:
I would suggest putting ALL constraints including primaries, at the end of the script and using "alter table" to add the constraints once all creation is done.

Not sure if you could turn off the foreign key constraints when running the create script like you do with the data script.

You can disable foreign key checks with set foregin_key_checks = 0;.  If set to 1 (the default), foreign key constraints for InnoDB  tables are checked. If set to 0, they are ignored. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different than that required by their parent/child relationships. For example:

mysql> set foreign_key_checks = 0;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `wrlscarr` (   `networkcompid` BIGINT(10) NOT NULL,   `networkid` BIGINT(10) NULL,   `comments` VARCHAR(255) NULL,   `nettype2compid` BIGINT(10) NOT NULL,   PRIMARY KEY (`networkcompid`),   CONSTRAINT `fk_net2comp` FOREIGN KEY `fk_net2comp` (`networkid`)     REFERENCES `wrlscarr`.`operator2network` (`networkid`) ) ENGINE = INNODB;
Query OK, 0 rows affected (0.23 sec)

Please reopen this.

The foreign key check is disabled for the data insertion sql file, but is not disabled in the table creation sql file.

The Create Object Online did disable the foreign key checks but the sql script did not have the foreign key checks disabled.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html