MySQL Error number 150 Create Fails on non existent foreign key

Question

I was wrestling with mysql administrator trying to change the primary key on a table. It wouldnt let me so I delete foureign keys and rename the table, great no problem.

I have now deleted the original table t_order_items but I cannot rename t_order_items2 to t_order_items, it fails with a 150 error.

SO i figured I would craft up the create statement and get out of administrator just in case it was the problem. The following create stament fails but only if the table is called t_order_items any other name and it succededs:

DROP TABLE IF EXISTS `web2print`.`t_order_items`;
CREATE TABLE  `web2print`.`t_order_items` (
  `orderID` bigint(20) NOT NULL,
  `productID` bigint(20) NOT NULL,
  `itemprice` decimal(10,2) NOT NULL,
  `itemvat` decimal(2,2) NOT NULL DEFAULT '0.15',
  `quantity` int(10) unsigned NOT NULL,
  `description` varchar(100) NOT NULL,
  `stockCode` varchar(45) DEFAULT NULL,
  `proofpath` varchar(300) DEFAULT NULL,
  `consignmentID` bigint(20) unsigned NOT NULL,
  `name` varchar(45) NOT NULL,
  `order_itemID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`order_itemID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If I check the output of SHOW ENGINE INNODB STATUS;

I get the following FOREIGN KEY ERROR report which is the correct time for the insert but references foreign keys from the old (original) table that has been deleted, and do not exist on this create script.

Error in foreign key constraint of table web2print/t_order_items: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint: , CONSTRAINT "FK_t_order_items_1" FOREIGN KEY ("orderID") REFERENCES "t_orders" ("orderID") ON DELETE CASCADE ON UPDATE CASCADE

There are no foreign keys on the table? they were deleted.

The following works

 SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `web2print`.`t_order_items`;
CREATE TABLE  `web2print`.`t_order_items` (
  `orderID` bigint(20) NOT NULL,
  `productID` bigint(20) NOT NULL,
  `itemprice` decimal(10,2) NOT NULL,
  `itemvat` decimal(2,2) NOT NULL DEFAULT '0.15',
  `quantity` int(10) unsigned NOT NULL,
  `description` varchar(100) NOT NULL,
  `stockCode` varchar(45) DEFAULT NULL,
  `proofpath` varchar(300) DEFAULT NULL,
  `consignmentID` bigint(20) unsigned NOT NULL,
  `name` varchar(45) NOT NULL,
  `order_itemID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`order_itemID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS = 1;

But why did I have to do that?

Thanks for any feedback

Answer 1

You had constraints between t_order_items and t_orders that were trying to validate. You had to first disable them before you drop the table. This is good because it protects you and your data. The Foreign keys might have been deleted, but the constraints were not.