Mysql – Foreign Key violation – not sure why

foreign keyMySQL

I have the following two tables with a foreign key on status_id:

mysql> describe usr_cookbook;
+-----------+-----------+------+-----+-------------------+-------+
| Field     | Type      | Null | Key | Default           | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| user_id   | int(11)   | NO   | PRI | NULL              |       |
| recipe_id | int(11)   | NO   | PRI | NULL              |       |
| status_id | int(11)   | NO   | MUL | NULL              |       |
| added_ts  | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-----------+-----------+------+-----+-------------------+-------+

mysql> describe usr_cookbook_status;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| status_id | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(45) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

There's two rows in usr_cookbook_status:

mysql> select * from usr_cookbook_status;
+-----------+-----------+
| status_id | name      |
+-----------+-----------+
|         1 | Try Soon  |
|         2 | Favorites |
+-----------+-----------+

So I should be good to insert into usr_cookbook right?

mysql> insert into usr_cookbook (user_id, recipe_id, status_id) values (3, 5, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint 
fails (`tomato`.`usr_cookbook`, CONSTRAINT `fk_cookbook_status` FOREIGN KEY 
(`status_id`)
REFERENCES `usr_cookbook_status` (`status_id`) ON DELETE NO ACTION ON UPDATE
NO ACTION)

Why the constraint violation? The status_id I'm using (1) exists in the parent table. I'm sure there's something simple I'm missing here…

Best Answer

When I looked at the DDL per Brian's suggestion the problem immediately became clear: The usr_cookbook_status table was using MyISAM engine. I switched it to InnoDB and everything works now.