Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL 5.5 add foreign key fails with errors [HY000][150] and [HY000][1005]

I have tried adding a foreign key like this...

ALTER TABLE OrderLineItem
ADD CONSTRAINT
        FK_OrderLineItem_ShippingType_name FOREIGN KEY
(shippingType)
REFERENCES ShippingType(name);

Or like this in Mysql 5.5...

alter table OrderLineItem add foreign key 
FK_OrderLineItem_ShippingType (shippingType) references ShippingType(name);

Every time I see the following error.

[2011-11-18 15:07:04] [HY000][150] Create table 'realtorprint_dev_dev/#sql-7d0_80' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

[2011-11-18 15:07:04] [HY000][1005] Can't create table 'realtorprint_dev_dev.#sql-7d0_80' (errno: 150)

Both OrderLineItem.shippingType and ShippingType.name have a type of varchar(50) not null. ShippingType.name is the primaryKey of ShippingType.

Here is the result of show create table on ShippingType as well as OrderLineItem...

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;
like image 966
benstpierre Avatar asked Nov 18 '11 22:11

benstpierre


People also ask

What is error 150 in MySQL?

Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table. Dropping an index required by a foreign key constraint. The foreign key constraint must be removed before dropping the index.

How to create foreign key in same table MySQL?

To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column. Follow that with the name of the referenced table and the name of the referenced column in parentheses.

What is enable foreign key checks?

Foreign Key Check in MySQL can prevent you from making some database updates to tables that have foreign key constraints. In such cases, you can temporarily disable foreign key check in MySQL, make your updates and enable foreign key check in MySQL afterwards. Here's how to disable foreign key constraint in MySQL.


1 Answers

It is possible is that Mysql gives this bad error when the column types do not match exactly - check collation / size etc.

like image 111
Adrian Cornish Avatar answered Oct 08 '22 11:10

Adrian Cornish