我有以下两个带有外键的表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 | |
+-----------+-------------+------+-----+---------+-------+
中有两行usr_cookbook_status
:
mysql> select * from usr_cookbook_status;
+-----------+-----------+
| status_id | name |
+-----------+-----------+
| 1 | Try Soon |
| 2 | Favorites |
+-----------+-----------+
所以我应该很好地插入 usr_cookbook 对吗?
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)
为什么违反约束?status_id
我正在使用的(1) 存在于父表中。我敢肯定这里缺少一些简单的东西...
当我根据 Brian 的建议查看 DDL 时,问题立即变得清晰:该
usr_cookbook_status
表使用的是 MyISAM 引擎。我将它切换到 InnoDB,现在一切正常。