MySQL 错误号尝试创建外键时为 150

Question

我有两张 table 。一个是带有 id 作为自动递增主键的“子类别”。

在第二个表“product_subcategory”中，我想让字段类别使用“subcategory.id”作为外键。

使用时

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

我收到错误编号。 150.

查找错误后，我发现 this 问题的答案说明这些条件可能导致错误号 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.

所以我使用 SHOW TABLE STATUS 来确认表是否合适:

 | 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 |                |         |

唯一的区别是 index_length。

各自的 PK 和 FK 列数据与我所看到的相同，两个表目前都是空的。

有没有人对可能导致此错误 150 的原因有任何建议？或者可以建议其他领域来帮助我解决这个问题？

编辑:确切的错误信息是:

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

DDL 信息:

> -- 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 */;

Answer 1

我试过了，发现您在 subCategory 的名称中输入了错误的大写字母。当您使用具有区分大小写文件名的操作系统时，MySQL 中的表名称区分大小写。

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)

参见 http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html了解更多详情。