多列唯一键关联单列外键触发SQL Error 1822的解决办法

错误原因 #

MySQL创建外键约束时，要求被引用的列必须是被引用表的主键，或者拥有独立的索引（普通索引、唯一索引均可）。

你遇到的SQL Error [1822]，核心原因是：kriterias表中，kode_kriteria列没有单独的索引——当前它仅作为组合唯一键(kelompok_kriteria_id, kode_kriteria)的一部分存在，无法单独被外键引用。

解决方案 #

根据业务逻辑，有两种可行的修复方式：

方案1：给kriterias表的kode_kriteria添加独立索引 #

如果kode_kriteria本身在业务上是全局唯一的（不依赖kelompok_kriteria_id就能唯一标识一条规则），可以给该列添加唯一索引；如果不是全局唯一，添加普通索引即可：

-- 添加普通索引（适用于kode_kriteria不全局唯一的场景）
ALTER TABLE kriterias ADD INDEX idx_kode_kriteria (kode_kriteria);

-- 或者添加唯一索引（适用于kode_kriteria全局唯一的场景）
ALTER TABLE kriterias ADD UNIQUE INDEX idx_unique_kode_kriteria (kode_kriteria);

添加索引后，执行修正后的关联表创建语句（注意补上原语句中两个CONSTRAINT之间的逗号）：

CREATE TABLE `rel_employee_kriterias` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint unsigned NOT NULL,
  `kode_kriteria` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nilai` int NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rel_employee_kriterias_employee_id_kode_kriteria_unique` (`employee_id`,`kode_kriteria`),
  CONSTRAINT `rel_employee_kriterias_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`),
  CONSTRAINT `rel_employee_kriterias_kode_kriteria_foreign` FOREIGN KEY (`kode_kriteria`) REFERENCES `kriterias` (`kode_kriteria`)
);

方案2：使用组合外键（更贴合原表设计） #

由于kriterias表的唯一标识是组合键(kelompok_kriteria_id, kode_kriteria)，说明kode_kriteria本身不具备全局唯一性，必须和kelompok_kriteria_id配合才能确定唯一一条记录。这种情况下，应该修改关联表，添加kelompok_kriteria_id列，创建组合外键：

CREATE TABLE `rel_employee_kriterias` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint unsigned NOT NULL,
  `kelompok_kriteria_id` bigint unsigned NOT NULL,
  `kode_kriteria` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nilai` int NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rel_employee_kriterias_employee_id_kelompok_kriteria_id_kode_kriteria_unique` (`employee_id`,`kelompok_kriteria_id`,`kode_kriteria`),
  CONSTRAINT `rel_employee_kriterias_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`),
  CONSTRAINT `rel_employee_kriterias_kriteria_foreign` FOREIGN KEY (`kelompok_kriteria_id`, `kode_kriteria`) REFERENCES `kriterias` (`kelompok_kriteria_id`, `kode_kriteria`)
);

这种方案更符合原表的设计逻辑，能确保外键引用的是kriterias表中唯一的一条记录，避免业务逻辑上的歧义。

内容的提问来源于stack exchange，提问作者Anang Hariyanto N