MySQL新增acceso表数据触发1452外键约束错误的解决

我最近在维护一个仅包含两张表的项目时，遇到了无法向acceso表新增数据的问题，明明categoria表的数据没做修改，ID也能匹配，却一直触发外键约束错误，折腾了好一会儿才找到根源，分享给大家参考：

现有表数据 #

先看一下当前两张表的现有数据：

-- 查询acceso表数据
select * from acceso;
+----+---------------+---------------------+-----------+-----------+------------+
| id | ip            | date                | bloqueado | categoria | comentario |
+----+---------------+---------------------+-----------+-----------+------------+
| 1  | 201.214.164.5 | 2018-05-31 01:16:10 | 0         | 1         | NULL       |
+----+---------------+---------------------+-----------+-----------+------------+

-- 查询categoria表数据
select * from categoria;
+----+---------+
| id | nombre  |
+----+---------+
| 1  | general |
+----+---------+

最初的建表脚本 #

我一开始使用的建表脚本是这样的：

drop table categoria;
drop table acceso;

CREATE TABLE categoria (
  cat_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL
);

CREATE TABLE acceso (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip VARCHAR(30) NOT NULL,
  date datetime NOT NULL,
  bloqueado tinyint(1) NOT NULL,
  categoria int(11) NOT NULL,
  comentario TEXT,
  FOREIGN KEY (id) REFERENCES categoria(cat_id)
);

遇到的错误 #

当尝试向acceso表插入数据时，直接触发了外键约束错误：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`c9`.`acceso`, CONSTRAINT `acceso_ibfk_1` FOREIGN KEY (`id`) REFERENCES `categoria` (`cat_id`))

错误原因排查 #

我反复确认categoria表的cat_id存在且和acceso要插入的categoria字段值匹配，但错误依然存在。后来仔细核对建表脚本才发现：外键关联的列完全写错了！

原来的脚本里，错误地把acceso表的主键id关联到了categoria表的cat_id，这完全不符合业务逻辑——我们需要的是用acceso表的categoria字段来关联categoria表的cat_id，以此表示每条访问记录所属的分类。

另外还有个细节问题：acceso表的categoria字段类型是int(11)，而categoria表的cat_id是INT(11) UNSIGNED，类型不匹配也可能导致潜在问题，所以修正时也统一了字段类型。

修正后的建表脚本 #

调整外键关联的字段，同时统一字段类型，修正后的脚本如下：

CREATE TABLE categoria (
  cat_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL
);

CREATE TABLE acceso (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip VARCHAR(30) NOT NULL,
  date datetime NOT NULL,
  bloqueado tinyint(1) NOT NULL,
  categoria int(11) UNSIGNED NOT NULL,
  comentario TEXT,
  FOREIGN KEY (categoria) REFERENCES categoria(cat_id)
);

修正后再插入数据就完全正常了，这个低级错误也提醒大家：创建外键时一定要仔细核对关联的字段，确保逻辑符合业务需求，同时要保证关联字段的类型完全一致。

内容的提问来源于stack exchange，提问作者ffuentes