MySQL Foreign Key Constraint

Question

I can't figure out this error:

Cannot add or update a child row: a foreign key constraint fails (freight.rates_detail, CONSTRAINT rates_detail_ibfk_2 FOREIGN KEY (component_id) REFERENCES computation (id) ON UPDATE CASCADE)

Here's what I'm trying to do(from the log):

INSERT INTO freight.rates_detail(rate_id,component_id,uom,rate,inc_or) values(0821,01,00,011.00,'Y')

I repeatedly looked and looked and reviewed the values of id in table computation and the value in that case: 01 exists.

What's the problem with this?

Check if component_id column is the same type adn length as the id column in computation. — Mihai
– Mihai, Commented Sep 16, 2013 at 13:49
Just a side note (not related to your problem): an int does not have "a length". The 11 is only a hint for a client application to be used when displaying the data. It does not limit the possible values in any way. You can store the same values in an int(1) and an int(11) — user330315
– user330315, Commented Sep 16, 2013 at 13:56
@eggshot: Yes, that will be the problem. MySQL requires both tables to use the same storage engine (and not MyISAM) in order for foreign key constraints to work. — eggyal
– eggyal, Commented Sep 16, 2013 at 14:14

eggyal · Accepted Answer · 2013-09-16 14:17:25Z

3

i just found out that the reference table is MyISAM not InnoDB will it affect?

Yes. As documented under Using FOREIGN KEY Constraints:

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

It goes on to say:

For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

answered Sep 16, 2013 at 14:17

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

Collectives™ on Stack Overflow

MySQL Foreign Key Constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related