MySQL error no. 150 when trying to create foreign key

Question

I have two tables. One is 'subcategory' with id as an auto-incrementing primary key.

From a second table, 'product_subcategory' I want to have the field category use 'subcategory.id' as a foreign key.

When using

ALTER TABLE Product_SubCategory ADD CONSTRAINT fk_subcategory_product 
FOREIGN KEY (subCategory) REFERENCES subCategory(id);

I receive error no. 150.

Having looked up the error I found this question with an answer that states these conditions can lead to error no 150:

1. The two tables must be ENGINE=InnoDB. (can be others: ENGINE=MyISAM works too)
2. The two tables must have the same charset.
3. The PK column(s) in the parent table and the FK column(s) must be the same data type.
4. The PK column(s) in the parent table and the FK column(s), if they have a define collation type, must have the same collation type;
5. If there is data already in the foreign key table, the FK column value(s) must match values in the parent table PK columns.
6. And the child table cannot be a temporary table.

So I used SHOW TABLE STATUS to confirm that the tables were suitable:

 | Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |

| SubCategory | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |   6291456 |           NULL | 2013-07-31 16:37:03 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |

&

     | Name                | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |

  | Product_SubCategory | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   6291456 |           NULL | 2013-07-31 16:35:04 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |

The only difference being the index_length.

The respective PK & FK column data is identical from what I can see and both tables are currently empty.

Does anyone have any suggestions about what may be causing this error no 150? Or can suggest other areas to look into to help me problem solve this?

EDIT: The exact error message is:

ERROR 1005 (HY000): Can't create table 'NTM.#sql-31c_365' (errno: 150)

DDL info:

> -- MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: NTM
-- ------------------------------------------------------
-- Server version   5.5.31-0ubuntu0.13.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `Product`
--

DROP TABLE IF EXISTS `Product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `tax` double DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `size` varchar(255) DEFAULT NULL,
  `discount` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_product_category` (`category`),
  CONSTRAINT `fk_product_category` FOREIGN KEY (`category`) REFERENCES `Category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Product_SubCategory`
--

DROP TABLE IF EXISTS `Product_SubCategory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Product_SubCategory` (
  `product` int(11) NOT NULL,
  `subCategory` int(11) DEFAULT NULL,
  PRIMARY KEY (`product`),
  CONSTRAINT `fk_product_subcategory` FOREIGN KEY (`product`) REFERENCES `Product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `SubCategory`
--

DROP TABLE IF EXISTS `SubCategory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `SubCategory` (
  `id` int(11) NOT NULL DEFAULT '0',
  `parent` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `text` longtext,
  `summary` longtext,
  `image` varchar(255) DEFAULT NULL,
  `featured` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_SC_parent_id` (`parent`),
  CONSTRAINT `fk_SC_parent_id` FOREIGN KEY (`parent`) REFERENCES `Category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Can you show table DDL information? Do you have pre-existing data in these tables that would break the foreign key constraint? — Mike Brant, Jul 31 '13 at 17:18
added mysqldump -d to original & full error message to original post. The tables are empty. — DJ_Beardsquirt, Jul 31 '13 at 17:28

score 0 · Accepted Answer · answered Jul 31 '13 at 17:50

I tried this and found that you mistyped the capitalization in the name of subCategory. Table names in MySQL are case-sensitive when you use an OS that has case-sensitive filenames.

mysql> SELECT VERSION();
+---------------+
| version()     |
+---------------+
| 5.6.12-56-log |
+---------------+

mysql> ALTER TABLE Product_SubCategory ADD CONSTRAINT fk_subcategory_product 
FOREIGN KEY (subCategory) REFERENCES subCategory(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

mysql> ALTER TABLE Product_SubCategory ADD CONSTRAINT fk_subcategory_product 
FOREIGN KEY (subCategory) REFERENCES SubCategory(id);
Query OK, 0 rows affected (0.33 sec)

See http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html for more details.

Thanks, good catch! Sloppy capitalization is a curse - I must have repeated the mistake at least three times whilst trying to resolve this. — DJ_Beardsquirt, Jul 31 '13 at 17:53

MySQL error no. 150 when trying to create foreign key

1 Answers1