Not able to add foreign key in MySql

Question

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?

wchiquito · Accepted Answer · 2018-03-04 12:22:24Z

2

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

...

answered Mar 4, 2018 at 12:22

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shiladittya Chakraborty Over a year ago

If I am trying to change engine then its saying table already exist. How to resolve this issue?

wchiquito Over a year ago

@ShiladittyaChakraborty: Try ALTER TABLE `TABLE1` ENGINE=InnoDB;, after, ALTER TABLE `TABLE2` ADD CONSTRAINT ....

Collectives™ on Stack Overflow

Not able to add foreign key in MySql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related