CODEWITHSUNDEEP
mysql
Shiladittya Chakraborty
Shiladittya Chakraborty

Reputation: 4418

Not able to add foreign key in MySql

CREATE TABLE `TABLE1` (
  `TABLE_ID1` varchar(4) NOT NULL,
  PRIMARY KEY (`TABLE_ID1`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `TABLE2` (
  `LOCAL_CUST_ID` int(10) NOT NULL,
  `TABLE_ID1` varchar(4) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Both table created successfully in MySql but while trying adding foreign key by below Alter statement then getting below issue.

ALTER TABLE TABLE2
ADD CONSTRAINT FK_ID FOREIGN KEY(TABLE_ID1) REFERENCES TABLE1(TABLE_ID1);

Error Code: 1452
Cannot add or update a child row: a foreign key constraint fails 
(`#sql-7fcd_79`, CONSTRAINT `FK_ID` FOREIGN KEY (`TABLE_ID1`) REFERENCES `TABLE1` (`TABLE_ID1`))

In both table I have used different engine.

Can someone please help what I am not able to add foreign key?

Upvotes: 0

Views: 51

Answers (1)

wchiquito
wchiquito

Reputation: 16559

See documentation:

13.1.18.6 Using FOREIGN KEY Constraints

...

Foreign keys definitions are subject to the following conditions:

...

  • ... The parent and child tables must use the same storage engine. ...

...

Upvotes: 2

Related Questions