SQL中设置两个不同ON DELETE行为的外键的建表语句如何编写

核心问题说明 #

你原有写法存在两处冲突：

• product_version_id 字段设置了 NOT NULL 非空约束，和该字段外键的 ON DELETE SET NULL 规则直接冲突：当关联的product表记录被删除时，数据库尝试将该字段赋值为NULL，会触发非空约束报错，建表语句无法执行。
• 原表主键包含product_version_id字段，MySQL要求主键的所有组成字段必须为非空，如果你要保留ON DELETE SET NULL规则，就不能将product_version_id纳入主键，需要调整主键逻辑。

可运行的正确建表语句示例 #

以下示例假设你业务允许调整主键为自增ID，同时保留你要求的两个外键规则：

CREATE TABLE `product_tag` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `product_id` BINARY(16) NOT NULL,
  `product_version_id` BINARY(16) NULL COMMENT '允许为空以支持ON DELETE SET NULL',
  `tag_id` BINARY(16) NOT NULL,
  PRIMARY KEY (`id`),
  -- 可选：添加唯一索引保证业务唯一性，根据你的业务需要调整
  UNIQUE KEY `uk_product_tag_unique` (`product_id`, `product_version_id`, `tag_id`),
  CONSTRAINT `fk.product_tag.product_id` FOREIGN KEY (`product_id`)
    REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk.product_tag.product_version_id` FOREIGN KEY (`product_version_id`)
    REFERENCES `product` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk.product_tag.tag_id` FOREIGN KEY (`tag_id`)
    REFERENCES `tag` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

特殊情况说明 #

如果你的业务要求product_version_id必须非空，那么ON DELETE SET NULL规则就无法落地，你需要调整外键删除规则为ON DELETE CASCADE/ON DELETE RESTRICT等不需要赋值NULL的选项，就可以保留原有的联合主键逻辑。

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