1

When I execute the following SQL commands:

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

CREATE TABLE `TableB` (
  `tableBId` INT(11) NOT NULL AUTO_INCREMENT,
  `tableAId` INT(11) DEFAULT NULL,
  PRIMARY KEY (`tableBId`),
  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`)
);

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`)
Cœur
  • 37,241
  • 25
  • 195
  • 267
blemale
  • 11
  • 2

1 Answers1

0

Old question but for others in a similar situation the answer I made at:

https://dba.stackexchange.com/a/87587/56052

may help.

  • Recreate the table with the same foreign key specification as previous but a different name.
  • Drop the resulting table (will also drop original orphan foreign key)
  • Recreate table with original or no foreign key
Community
  • 1
  • 1
Mark C
  • 1,314
  • 1
  • 12
  • 19