CODEWITHSUNDEEP
phpmysql
Pankaj
Pankaj

Reputation: 10105

Issue while creating the Foreign Key Constraint : MySQL

Category Table

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 ;

Sub Category Table

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 ;

Below is the Query I tried to add Foreign key Reference in Sub Catgeory Table

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

MySQL said

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

Question

Am I missing something ?

Upvotes: 0

Views: 48

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300599

You need to reference Category table, not the SubCategory table:

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

Upvotes: 1

Patrick Lee
Patrick Lee

Reputation: 2013

I think you meant to reference CategoryID in the other table...

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

Upvotes: 1

Related Questions