MySQL ERROR 1215 Cannot add foreign key constraint:26.10 上多数情形已改报 3780、6125、1824,只有临时表还报 1215

← 上一篇 MySQL ERROR 1055 ONLY_FULL_GROUP_BY:关掉它拿到的 name 是组里任意一行,换个索引结果就变

类型不兼容

实测 · 本机执行输出

  父 int / 子 bigint:(3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
      SHOW WARNINGS: ('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  父 bigint / 子 int:(3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
      SHOW WARNINGS: ('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  父 int unsigned / 子 int(有符号):(3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
      SHOW WARNINGS: ('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  父 int / 子 int unsigned:(3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
      SHOW WARNINGS: ('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  父 varchar(20) utf8mb4_0900_ai_ci / 子 varchar(20) utf8mb4_bin:(3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
      SHOW WARNINGS: ('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  父 varchar(20) utf8mb4 / 子 varchar(20) latin1:(3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
      SHOW WARNINGS: ('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  父 varchar(20) / 子 varchar(50)(同字符集,长度不同):成功
  父 varchar(20) / 子 char(20):成功
  父 decimal(10,2) / 子 decimal(10,3):成功
  父 date / 子 datetime:(3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
      SHOW WARNINGS: ('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  父 int / 子 bigint,且 foreign_key_checks = 0:(3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
      SHOW WARNINGS: ('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  父 int / 子 int(类型一致,对照):成功

符号、字符集、排序规则不同都是 3780;varchar 长度不同和 decimal 小数位不同却能通过。foreign_key_checks=0 绕不过这种检查。

索引与表的问题

实测 · 本机执行输出

  父表被引用的列没有索引:(6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'fk' in the referenced table 'p'")
      SHOW WARNINGS: ('Error', 6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'fk' in the referenced table 'p'")
  父表被引用的列有普通索引(不是唯一):(6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'fk' in the referenced table 'p'")
      SHOW WARNINGS: ('Error', 6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'fk' in the referenced table 'p'")
  父表联合主键 (a, b),子表只引用 b:(6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'fk' in the referenced table 'p'")
      SHOW WARNINGS: ('Error', 6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'fk' in the referenced table 'p'")
  父表联合主键 (a, b),子表引用 (a):(6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'fk' in the referenced table 'p'")
      SHOW WARNINGS: ('Error', 6125, "Failed to add the foreign key constraint. Missing unique key for constraint 'fk' in the referenced table 'p'")
  父表是 MyISAM:(1824, "Failed to open the referenced table 'p'")
      SHOW WARNINGS: ('Error', 1824, "Failed to open the referenced table 'p'")
  父表不存在:(1824, "Failed to open the referenced table 'nope'")
  约束名 fk 在同一个库里已被 c1 用过,c2 又用 fk:(1826, "Duplicate foreign key constraint name 'fk'")
  ON DELETE SET NULL 但子列 NOT NULL:(1830, "Column 'pid' cannot be NOT NULL: needed in a foreign key constraint 'fk' SET NULL")
      SHOW WARNINGS: ('Error', 1830, "Column 'pid' cannot be NOT NULL: needed in a foreign key constraint 'fk' SET NULL")

被引用的列必须是唯一键的完整列(6125);父表是 MyISAM 或不存在是 1824;约束名要在整个库里唯一(1826);SET NULL 要求子列可空(1830)。

还报 1215 的情形

实测 · 本机执行输出

  子表是临时表(CREATE):(1215, 'Cannot add foreign key constraint')
  子表是临时表(ALTER 加外键):(1215, 'Cannot add foreign key constraint')
  父表是视图:(1824, "Failed to open the referenced table 'p'")
  父表是临时表:(1824, "Failed to open the referenced table 'p'")
  父表是 MEMORY 引擎:(1824, "Failed to open the referenced table 'p'")
  引用父表里不存在的列:(3734, "Failed to add the foreign key constraint. Missing column 'nocol' for constraint 'fk' in the referenced table 'p'")
  子表里不存在的外键列:(1072, "Key column 'nocol' doesn't exist in table")
  外键列数和引用列数不一致:(1239, "Incorrect foreign key definition for 'fk': Key reference and table reference don't match")
  分区表:(1506, 'Foreign keys are not yet supported in conjunction with partitioning')
  外键列是虚拟生成列:(3733, "Foreign key 'fk' uses virtual column 'pid' which is not supported.")
  外键列是 stored 生成列 + ON UPDATE CASCADE:(3104, 'Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.')
  子表是 MyISAM(对照):ok
  MyISAM 子表建成功之后,information_schema 里它的 FOREIGN KEY 约束有 0 个;往里插一个没有父行的 pid=999 -> ok(外键被悄悄忽略)

子表是临时表还是 1215;其余限制各有码。子表是 MyISAM 不会报错但外键没有生效。

不用再去翻 INNODB STATUS

实测 · 本机执行输出

  create table c(pid bigint ...) -> (3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")
  紧接着 show warnings: [('Error', 3780, "Referencing column 'pid' and referenced column 'id' in foreign key constraint 'fk' are incompatible.")]
  SHOW ENGINE INNODB STATUS 里有 LATEST FOREIGN KEY ERROR 一节吗: 没有
  对照:建表成功 -> ok

报错文本已经说明原因,SHOW WARNINGS 是同一条;这个版本的 SHOW ENGINE INNODB STATUS 里没有 LATEST FOREIGN KEY ERROR 一节。

已有脏数据加外键

实测 · 本机执行输出

  子表里 pid 在父表中不存在的行: ((12, 99), (13, 98))
  alter table c add constraint fk ... -> (1452, 'Cannot add or update a child row: a foreign key constraint fails (`d`.`#sql-8de9_b`, CONSTRAINT `fk` FOREIGN KEY (`pid`) REFERENCES `p` (`id`))')
  自查孤儿行的语句:select c.* from c left join p on p.id = c.pid where p.id is null -> ((12, 99), (13, 98))
  set foreign_key_checks = 0 之后再 add constraint -> ok
  外键存在了,孤儿行还在: ((13, 98), (12, 99))
  之后对孤儿行做不相关的 UPDATE(set id = id)-> ok
  把孤儿行的 pid 改成另一个不存在的值 -> (1452, 'Cannot add or update a child row: a foreign key constraint fails (`d`.`c`, CONSTRAINT `fk` FOREIGN KEY (`pid`) REFERENCES `p` (`id`))')

子表里有孤儿行时 ALTER 报 1452,先用 LEFT JOIN 自查。关掉 foreign_key_checks 能加上,但孤儿行仍在,日后改动它们的外键列会触发 1452。

运行期的 1452 与 1451

实测 · 本机执行输出

  insert into c values (11, 99) -> (1452, 'Cannot add or update a child row: a foreign key constraint fails (`d`.`c`, CONSTRAINT `fk` FOREIGN KEY (`pid`) REFERENCES `p` (`id`))')
  delete from p where id = 1 -> (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`d`.`c`, CONSTRAINT `fk` FOREIGN KEY (`pid`) REFERENCES `p` (`id`))')
  on delete cascade:delete from p where id = 1 -> ok;子表现在 ((11, 2),)
  on delete set null:delete from p where id = 1 -> ok;子表现在 ((10, None), (11, 2))

1452 是子表写入时父行不存在,1451 是删除父行时子行还在引用。CASCADE 连带删除子行,SET NULL 保留子行并置空外键列。

分步演示

外键创建失败

本步的对象

本文用到的工具与边界

本机 macOS 27 arm64 / MySQL 26.10.0 与 Redis 8.10.2(Homebrew,每个实验自己起实例,端口 9150 以上,只监听 127.0.0.1,不碰本机已有的实例)/ Python 3.15.0 + PyMySQL(按各篇注明用到哪些及版本);报错文本都是真实复现时原样捕获的;计时用 time.perf_counter,数字每次运行会有小幅抖动。本篇用一个自己起的 MySQL 26.10.0 实例(默认设置,InnoDB),PyMySQL 的 unix socket 连接;每个例子都新建父表 p 与子表 c,逐条记录报错码与文本
没有验证:MySQL 5.7 / 8.0 / 8.4 上同样情形报什么码(尤其哪些版本仍把它们统一报成 1215);restrict_fk_on_non_standard_key 之类控制“引用非唯一键”的开关;跨库引用、跨字符集的更多组合;外键与大表在线 DDL 的锁行为;MariaDB / TiDB 等兼容库的行为;ORM(Django、Hibernate、Flyway)生成 DDL 时的列类型选择

下一篇 · 常见报错 9

MySQL ERROR 2006 server has gone away 与 2013 Lost connection:各种断法报什么,应答丢了重试为什么会多出一行