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

Question

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;

I missed the bit about it being the primary key. Let me check — Adrian Cornish, Nov 18 '11 at 22:18
Works for me - are the columns exactly the same type in both tables? I've seen this when there is a slight type mismatch (collation etc) — Adrian Cornish, Nov 18 '11 at 22:21
Cool - I updated my answer to include - I spent a few hours looking at this one once in mysql :-) which is why I remember. — Adrian Cornish, Nov 18 '11 at 22:26

Adrian Cornish · Accepted Answer · 2011-11-18T22:26:16.840

40

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

edited Nov 18 '11 at 22:26

answered Nov 18 '11 at 22:13

Adrian Cornish

23,227
13
61
77

2

It's true! Dammint "unsigned" in "create table" statement! Thank you! – orafaelreis May 13 '15 at 18:31
Don't think guys that the error tells nothing about the problem, I consider this a bad programming style ! – Adelin Aug 22 '15 at 13:59
1

This solved it for me -- interesting that this same issue doesn't throw an error in MariaDB v10 – qotsa42 Jan 11 '23 at 22:34

score 10 · Answer 2 · edited Jun 29 '13 at 17:07

10

orderLineItem.shippingType has character set utf8, but ShippingType.name has character set latin1. These are not compatible for the purposes of foreign key references.

edited Jun 29 '13 at 17:07

fthiella

48,073
15
90
106

answered Nov 18 '11 at 22:41

Bill Karwin

538,548
86
673
828

we got same error with different column collation. you can check reference table column collation with SHOW FULL COLUMNS FROM tableName; – Fuad All Jul 16 '20 at 22:32

score -2 · Answer 3 · answered Oct 30 '20 at 13:10

-2

Data type mismatches or PK not declared properly on referenced based tables.

answered Oct 30 '20 at 13:10

user14549100

1

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

3 Answers3

Linked