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.