php - 创建外键约束时出现问题 : MySQL

Question

类别表

CREATE TABLE IF NOT EXISTS `tblcategory` (
  `CategoryID` int(11) NOT NULL AUTO_INCREMENT,
  `Category` varchar(25) NOT NULL,
  `IsActive` tinyint(1) NOT NULL,
  PRIMARY KEY (`CategoryID`),
  UNIQUE KEY `UK_tblcategory_Category` (`Category`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

子类别表

CREATE TABLE IF NOT EXISTS `tblsubcategory` (
  `SubCategoryID` int(11) NOT NULL AUTO_INCREMENT,
  `SubCategory` varchar(25) NOT NULL,
  `CategoryID` int(11) NOT NULL,
  `IsActive` int(11) NOT NULL,
  PRIMARY KEY (`SubCategoryID`),
  UNIQUE KEY `UK_tblSubCategory_SubCategory_CategoryID` (`CategoryID`,`SubCategory`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

下面是我尝试在子类别表中添加外键引用的查询

ALTER TABLE tblsubcategory
ADD CONSTRAINT FK_tblsubcategory_tblcategory_CategoryID
FOREIGN KEY (CategoryID)
REFERENCES tblsubcategory(CategoryID)

MySQL说

1005 - Can't create table myapp.#sql-6b8_33 (errno: 150 "Foreign key constraint is incorrectly formed")

问题

我错过了什么吗？

Answer 1

我认为您的意思是在其他表中引用CategoryID...

ALTER TABLE tblsubcategory
ADD CONSTRAINT FK_tblsubcategory_tblcategory_CategoryID
FOREIGN KEY (CategoryID)
REFERENCES tblcategory(CategoryID)