在MariaDB中添加允许空值的复合外键遇错误，如何解决？

问题场景 #

现有两张InnoDB引擎表：
entity表结构：

CREATE TABLE `entity` (
  `id` varchar(36) NOT NULL,
  `type` varchar(36) NOT NULL,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`,`type`)
);

entity_role表结构：

CREATE TABLE `entity_role` (
  `id` varchar(36) NOT NULL,
  `entity_type` varchar(36) DEFAULT NULL,
  `user_id` varchar(36) NOT NULL,
  `role_id` varchar(36) NOT NULL,
  `entity_id` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_entity_role_user_id` (`user_id`),
  KEY `FK_entity_role_role_id` (`role_id`),
  CONSTRAINT `FK_entity_role_role_id` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
  CONSTRAINT `FK_entity_role_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

尝试添加复合外键时触发报错：

alter table entity_role add constraint `FK_entity_role_entity`
  foreign key(`entity_id`, `entity_type`)
  references `entity`(`id`, `type`)
  on delete cascade;

[Code: 1005, SQL State: HY000] (conn=26) Can't create table entity_role (errno: 150 "Foreign key constraint is incorrectly formed")

错误原因及解决步骤 #

• 外键字段非空属性不匹配
  entity表的id和type均为NOT NULL，但entity_role的entity_id和entity_type是DEFAULT NULL。若要创建严格的外键约束，需修改字段为非空：

  ALTER TABLE entity_role MODIFY `entity_id` varchar(36) NOT NULL;
  ALTER TABLE entity_role MODIFY `entity_type` varchar(36) NOT NULL;
  

• 外键字段缺少对应索引
  InnoDB要求外键关联字段必须存在索引（单独索引或联合索引），需先创建联合索引：

  ALTER TABLE entity_role ADD INDEX idx_entity (entity_id, entity_type);
  

• 字段字符集/排序规则不一致
  需确保entity.id与entity_role.entity_id、entity.type与entity_role.entity_type的字符集、排序规则完全一致。可通过以下语句检查：

  SHOW FULL COLUMNS FROM entity LIKE 'id';
  SHOW FULL COLUMNS FROM entity_role LIKE 'entity_id';
  

  若不一致，修改entity_role的字段属性匹配entity表：

  -- 示例：改为utf8mb4字符集和utf8mb4_unicode_ci排序规则
  ALTER TABLE entity_role MODIFY `entity_id` varchar(36) NOT NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  ALTER TABLE entity_role MODIFY `entity_type` varchar(36) NOT NULL CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

• 现有数据违反外键约束
  若entity_role表中存在entity_id+entity_type组合在entity表中不存在的记录，需先清理：

  DELETE FROM entity_role
  WHERE NOT EXISTS (
      SELECT 1 FROM entity
      WHERE entity.id = entity_role.entity_id AND entity.type = entity_role.entity_type
  );
  

最终执行添加外键 #

完成上述步骤后，重新执行原添加外键语句即可成功。

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