添加外键触发errno:150错误，求排查及解决办法

在MariaDB 10.4.28（phpMyAdmin环境）中，尝试为Perjalanan_Dinas表添加外键时触发errno: 150 'Foreign key constraint is incorrectly formed'错误，已确认外键列数据类型一致且已添加索引，仍无法解决。

引用表Kegiatan的创建语句 #

CREATE TABLE `Kegiatan` (
  `id_kegiatan` int(11) NOT NULL AUTO_INCREMENT,
  `id_program` int(11) NOT NULL,
  `nama_kegiatan` varchar(15) NOT NULL,
  PRIMARY KEY (`id_kegiatan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

待添加外键的Perjalanan_Dinas表创建语句 #

CREATE TABLE `Perjalanan_Dinas` (
  `id_dinas` int(11) NOT NULL AUTO_INCREMENT,
  `id_kegiatan` int(11) NOT NULL,
  `id_satker` int(11) NOT NULL,
  `nomor_surat_tugas` varchar(30) DEFAULT NULL,
  `nomor_sp2d` varchar(20) DEFAULT NULL,
  `tanggal_surat_tugas` date DEFAULT NULL,
  `tanggal_mulai_dinas` date DEFAULT NULL,
  `tanggal_selesai_dinas` date DEFAULT NULL,
  `tujuan_dinas` text DEFAULT NULL,
  PRIMARY KEY (`id_dinas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

执行的外键添加语句 #

ALTER TABLE Perjalanan_Dinas ADD CONSTRAINT FOREIGN_id_kegiatan FOREIGN KEY (`id_kegiatan`) REFERENCES `Kegiatan`(`id_kegiatan`) ON DELETE CASCADE ON UPDATE CASCADE;

触发的错误信息 #

#1025 - Error on rename of './database/#sql-50ec_548' to './database/Perjalanan_Dinas' (errno: 150 "Foreign key constraint is incorrectly formed")

可能原因及解决步骤 #

1. 子表存在无效的外键值

外键约束创建时会校验子表中所有外键列的值是否都存在于父表的主键列中，如果存在父表没有的值，会直接触发错误。

执行以下SQL检查无效数据：

SELECT pd.id_kegiatan 
FROM Perjalanan_Dinas pd
LEFT JOIN Kegiatan k ON pd.id_kegiatan = k.id_kegiatan
WHERE k.id_kegiatan IS NULL;

• 如果查询返回结果，说明存在无效的id_kegiatan值，需要删除这些行，或者将对应的id_kegiatan修改为Kegiatan表中已存在的值。

2. 验证存储引擎一致性

虽然两张表的创建语句都指定了ENGINE=InnoDB，但可以再次确认：

SHOW TABLE STATUS LIKE 'Kegiatan';
SHOW TABLE STATUS LIKE 'Perjalanan_Dinas';

确保两张表的Engine字段均为InnoDB（MyISAM不支持外键）。

3. 确认字符集与排序规则完全一致

执行以下SQL检查两张表的字符集和排序规则：

SELECT CCSA.character_set_name, CCSA.collation_name 
FROM information_schema.`TABLES` T,
     information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = DATABASE()
  AND T.table_name IN ('Kegiatan', 'Perjalanan_Dinas');

确保两张表的字符集和排序规则完全匹配（当前代码中显示一致，但实际环境可能存在差异）。

4. 重新执行外键添加语句

确认上述问题解决后，重新执行外键添加语句（可优化约束命名以提高可读性）：

ALTER TABLE Perjalanan_Dinas 
ADD CONSTRAINT fk_perjalan_dinas_kegiatan 
FOREIGN KEY (`id_kegiatan`) 
REFERENCES `Kegiatan`(`id_kegiatan`) 
ON DELETE CASCADE 
ON UPDATE CASCADE;

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