Bug #69364 Error in foreign key constraint on a droped table

• Submitted: 31 May 2013 12:23
• Modified: 31 May 2013 13:24
• Reporter: Bastien LEMALE
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.42
• OS: Windows (Win7)
• CPU Architecture: Any

[31 May 2013 12:23] Bastien LEMALE

Description:
See below.

How to repeat:
CREATE TABLE `TableA` (
  `tableAId` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`tableAId`)
);

CREATE TABLE `TableB` (
  `paymentProviderId` INT(11) NOT NULL AUTO_INCREMENT,
  `tableAId` INT(11) DEFAULT NULL,
  PRIMARY KEY (`paymentProviderId`),
  CONSTRAINT `FK_TABLE_A_ID` FOREIGN KEY (`tableAId`) REFERENCES `TableA` (`tableAId`),
);

ALTER TABLE `TableB`
	RENAME TO `NewTableB`;
    
ALTER TABLE `TableA`
	RENAME TO `NewTableA`,
	CHANGE COLUMN `tableAId` `newTableAId` INT(11) NOT NULL AUTO_INCREMENT FIRST;
    
DROP TABLE IF EXISTS NewTableA;

DROP TABLE IF EXISTS NewTableB;

CREATE TABLE `TableA` (
  `tableAId` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`tableAId`)
);

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130531 12:06:05 Error in foreign key constraint of table TableB:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT `FK_TABLE_A_ID` FOREIGN KEY (`tableAId`) REFERENCES `TableA` (`tableAId`)

[31 May 2013 12:38] Bastien LEMALE

To be complete :

I have the following error on the last command (ie CREATE TABLE TableA (...)) :

Erreur SQL (1005): Can't create table 'TableA' (errno: 150) Foreign key constraint is incorrectly formed

And when I execute show engine innodb status I have :

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130531 12:06:05 Error in foreign key constraint of table TableB:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT `FK_TABLE_A_ID` FOREIGN KEY (`tableAId`) REFERENCES `NewTableA` (`tableAId`)

[31 May 2013 13:24] MySQL Verification Team

Please upgrade with the lasted release version. Thanks.

[miguel@tikal 5.1]$ bin/mysql -uroot dx             
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4                            
Server version: 5.1.71-debug Source distribution         

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective    
owners.                                                          

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `TableA` (
    ->   `tableAId` INT(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`tableAId`)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE `TableB` (
    ->   `paymentProviderId` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `tableAId` INT(11) DEFAULT NULL,
    ->   PRIMARY KEY (`paymentProviderId`),
    ->   CONSTRAINT `FK_TABLE_A_ID` FOREIGN KEY (`tableAId`) REFERENCES `TableA` (`tableAId`));
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE `TableB`
    -> RENAME TO `NewTableB`;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `TableA`
    -> RENAME TO `NewTableA`,
    -> CHANGE COLUMN `tableAId` `newTableAId` INT(11) NOT NULL AUTO_INCREMENT FIRST;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS NewTableA;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS NewTableB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `TableA` (
    ->   `tableAId` INT(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`tableAId`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>