Error 1050 when trying to add foreign key constraing in MySQL

Question

I have tried adding a column to my UserOrder table called discountcode. This is a nullable foreign key into

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

The error happens on the second line. Both tables are running InnoDB. I am on MySQL 5.5.11.

Here is the error log...

[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

Here is "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'

Here is show create table 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 'SHOW ENGINE INNODB STATUS' command may explain this situation. — Devart, May 30 '12 at 06:38

score 1 · Answer 1 · answered May 30 '12 at 08:32

1

As stated in 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.

Have you defined an index on the referenced key DiscountCode.code?

answered May 30 '12 at 08:32

eggyal

122,705
18
212
237

DiscountCode.code is a primary key... Wouldn't that have an index itself already? – benstpierre May 30 '12 at 18:04
1

@Benju: Yes, it would... can you edit your question to include the outputs of `SHOW CREATE TABLE UserOrder` and `SHOW CREATE TABLE DiscountCode`? – eggyal May 30 '12 at 18:17

Error 1050 when trying to add foreign key constraing in MySQL

1 Answers1