mysql 5.5 外键_MySQL 5.5添加外键失败，错误[HY000] [150]和[HY000] [1005]

我试过像这样添加一个外键......

ALTER TABLE OrderLineItem

ADD CONSTRAINT

FK_OrderLineItem_ShippingType_name FOREIGN KEY

(shippingType)

REFERENCES ShippingType(name);

或者在Mysql 5.5中这样...

alter table OrderLineItem add foreign key

FK_OrderLineItem_ShippingType (shippingType) references ShippingType(name);

每次我看到以下错误 .

[2011-11-18 15:07:04] [HY000] [150]使用外键约束创建表'realtorprint_dev_dev / #sql-7d0_80'失败 . 引用的表中没有索引，其中引用的列显示为第一列 . [2011-11-18 15:07:04] [HY000] [1005]无法创建表'realtorprint_dev_dev . ＃sql-7d0_80'(错误号：150)

OrderLineItem.shippingType和ShippingType.name都具有varchar(50)类型，而不是null . ShippingType.name是ShippingType的primaryKey .

以下是关于ShippingType以及OrderLineItem的show create table的结果...

CREATE TABLE `shippingtype` (

`name` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',

`description` varchar(255) CHARACTER SET latin1 NOT NULL,

PRIMARY KEY (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orderlineitem` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`description` varchar(255) CHARACTER SET latin1 NOT NULL,

`lineNumber` int(11) NOT NULL,

`quantity` int(11) NOT NULL,

`quantityMultiplier` int(11) NOT NULL,

`unitPrice` decimal(10,2) NOT NULL,

`order_id` bigint(20) NOT NULL,

`productDefinition_id` bigint(20) NOT NULL,

`mlsId` varchar(255) CHARACTER SET latin1 DEFAULT NULL,

`printProviderUnitCost` decimal(10,2) NOT NULL,

`shippingType` varchar(50) NOT NULL,

`address` varchar(255) DEFAULT NULL,

`zipPostal` varchar(255) NOT NULL,

`city` varchar(255) NOT NULL,

`stateProvince` varchar(255) NOT NULL,

`country` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_OrderLineItem_productDefinition_id` (`productDefinition_id`),

KEY `idx_OrderLineItem_order_id` (`order_id`),

CONSTRAINT `FK_OrderLineItem_order_id` FOREIGN KEY (`order_id`) REFERENCES `userorder` (`id`),

CONSTRAINT `FK_OrderLineItem_productDefinition_id` FOREIGN KEY (`productDefinition_id`) REFERENCES `productdefinition` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10029 DEFAULT CHARSET=utf8;