我已经尝试在我的 UserOrder 表中添加一个名为 discountcode 的列。这是

alter table UserOrder add column discountCode varchar(100) null;
alter table UserOrder add foreign key FK_UserOrder_DiscountCode_code(`discountCode`) references DiscountCode(`code`);

错误发生在第二行。两个表都在运行 InnoDB。我在 MySQL 5.5.11 上。

这是错误日志...

[2012-05-29 23:59:07] [42S01][1050] Table '.\realtorprint_dev_dev\userorder' already exists
[2012-05-29 23:59:07] [HY000][1025] Error on rename of '.\realtorprint_dev_dev\#sql-28a4_3' to '.\realtorprint_dev_dev\userorder' (errno: -1)
[2012-05-29 23:59:07] [42S01][1050] Table '.\realtorprint_dev_dev\userorder' already exists

这里是“show create Table UserOrder”

'CREATE TABLE `userorder` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `paymentTxID` varchar(255) DEFAULT NULL,
  `shippedDate` datetime DEFAULT NULL,
  `shippingTrackingNumber` varchar(255) DEFAULT NULL,
  `taxType` varchar(255) NOT NULL,
  `taxValue` decimal(10,2) NOT NULL,
  `orderStatus` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `stateProvince` varchar(255) NOT NULL,
  `zipPostal` varchar(255) NOT NULL,
  `paymentType` varchar(255) NOT NULL,
  `backendUserId` bigint(20) DEFAULT NULL,
  `adjustedTotalPrice` decimal(10,2) DEFAULT NULL,
  `adjustedPrinterPrice` decimal(10,2) DEFAULT NULL,
  `adminNotes` varchar(2048) DEFAULT NULL,
  `printerBillStatus` varchar(40) NOT NULL,
  `userInvoiceStatus` varchar(40) NOT NULL,
  `expeditedAddressDescription` varchar(255) DEFAULT NULL,
  `shippingType` varchar(50) NOT NULL,
  `shippingCost` decimal(10,2) NOT NULL,
  `discountCode` varchar(100) DEFAULT NULL,
  `discountAmount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_UserOrder_user_id` (`user_id`),
  KEY `idx_UserOrder_orderStatus_id` (`orderStatus`),
  KEY `FK_UserOrder_PaymentType` (`paymentType`),
  KEY `FK_UserOrder_PrinterBillStatus_Name` (`printerBillStatus`),
  KEY `FK_UserOrder_UserInvoiceStatus_Name` (`userInvoiceStatus`),
  KEY `FK_UserOrder_User` (`backendUserId`),
  KEY `FK_UserOrder_ShippingType_name` (`shippingType`),
  CONSTRAINT `FK_UserOrderBcknd_User` FOREIGN KEY (`backendUserId`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_UserOrder_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `userorder_ibfk_1` FOREIGN KEY (`shippingType`) REFERENCES `shippingtype` (`name`),
  CONSTRAINT `UserOrder_ibfk_2` FOREIGN KEY (`paymentType`) REFERENCES `paymenttype` (`name`),
  CONSTRAINT `UserOrder_ibfk_6` FOREIGN KEY (`printerBillStatus`) REFERENCES `printerbillstatus` (`name`),
  CONSTRAINT `UserOrder_ibfk_7` FOREIGN KEY (`userInvoiceStatus`) REFERENCES `userinvoicestatus` (`name`),
  CONSTRAINT `UserOrder_ibfk_8` FOREIGN KEY (`orderStatus`) REFERENCES `orderstatus` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=21412 DEFAULT CHARSET=utf8'

这里是显示创建表 DiscountCode...

'CREATE TABLE `discountcode` (
  `code` varchar(100) NOT NULL,
  `percent` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `expires` datetime NOT NULL,
  `maxUses` int(11) NOT NULL,
  `useCount` int(11) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8'

最佳答案

如 the manual 中所述:

• InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

您是否在引用的键 DiscountCode.code 上定义了索引？

关于mysql - 尝试在 MySQL 中添加外键约束时出现错误 1050，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10810711/