无法添加外键约束错误1215 phpmyadmin

Currently working with a database in phpmyadmin and I'm trying to set up a foreign key constraint. Parent Table:

目前正在使用phpmyadmin中的数据库，我正在尝试设置外键约束。父表：

CREATE TABLE IF NOT EXISTS `productcategory` (
  `ID` int(11) NOT NULL,
  `Name` varchar(64) NOT NULL,
  `Description` text NOT NULL,
  `categoryManager` varchar(64) NOT NULL,
  `emailAddress` varchar(64) NOT NULL,
  `managerPhoneNum` tinyint(10) NOT NULL,
  `managerDateAppointed` date NOT NULL,
  `managerLocation` varchar(64) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;



INSERT INTO `productcategory` (`ID`, `Name`, `Description`, `categoryManager`, `emailAddress`, `managerPhoneNum`, `managerDateAppointed`, `managerLocation`) VALUES
(2, 'General Fiction', 'General Fiction from all aspects, you''ll find authors such as, George RR Martin, Cormac McCarthy and so forth ', '', '', 0, '0000-00-00', ''),
(4, '', '', '', '', 0, '0000-00-00', ''),
(5, '', '', 'localhost', '2016-01-21', 0, '0000-00-00', 'Crumlin'),
(6, '', '', 'Test', '2016-02-18', 0, '0000-00-00', 'Dun Laoighre'),
(7, '', '', 'hchchcvh', '2016-02-11', 0, '0000-00-00', 'Gorey'),
(8, '', '', 'localhost', '', 0, '0000-00-00', '');


ALTER TABLE `productcategory`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `ID` (`ID`);


ALTER TABLE `productcategory`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9;

Child table:

儿童桌：

CREATE TABLE IF NOT EXISTS `product` (
  `ProductID` int(11) NOT NULL,
  `AuthorName` varchar(64) NOT NULL,
  `BookName` varchar(64) NOT NULL,
  `CostPrice` int(11) NOT NULL,
  `sellPrice` int(11) NOT NULL,
  `productCatID` int(11) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;



INSERT INTO `product` (`ProductID`, `AuthorName`, `BookName`, `CostPrice`, `sellPrice`, `productCatID`) VALUES
(4, 'Cormac McCarthy', 'The Road', 9, 11, 2),
(9, 'J.R Tolkein', 'The Two Towers', 3, 6, 2);


ALTER TABLE `product`
  ADD PRIMARY KEY (`ProductID`),
  ADD KEY `productCatID` (`productCatID`);



ALTER TABLE `product`
  MODIFY `ProductID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12;

And here is the query I'm running to add the constraint:

这是我正在运行的查询添加约束：

`ALTER TABLE `product` ADD  FOREIGN KEY (`productCatID`)
 REFERENCES `highland_books_database`.`productcategory`(`ID`) ON DELETE          RESTRICT ON UPDATE RESTRICT;`

I have indexed the primary key of each tables. I have made sure they are both using InnoDB and I have set up the correct relational view for each table but when I go to add a foreign key constraint on the productCat ID row in the product table I keep getting this error message. I've tried reading the documentation but cant find what my problem is.

我已经索引了每个表的主键。我已经确定他们都使用InnoDB并且我已经为每个表设置了正确的关系视图但是当我在product表中的productCat ID行添加外键约束时，我不断收到此错误消息。我试过阅读文档，但无法找到我的问题。

1 个解决方案

#1

Foreign keys:

外键：

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

外键和引用键中的相应列必须具有相似的数据类型。整数类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。

You need to have the same datatype:

您需要具有相同的数据类型：

productcategory => `ID` int(11) NOT NULL

vs.

与

product => `productCatID` int(11) unsigned NOT NULL

Remove unsigned from column definition.

从列定义中删除无符号。

SqlFiddleDemo

SqlFiddleDemo

#1