MySQL如何让orders表字段关联services、products两张表的主键？

报错原因说明 #

你给同一个字段同时添加指向services和products两张表的外键会触发1452错误，是因为MySQL外键约束是与逻辑：要求字段值必须同时存在于所有关联父表的对应字段中，而你的需求是或逻辑（只要存在于任意一张父表即可），因此原生外键功能无法直接实现你的需求。

可行解决方案 #

方案1：CHECK约束+自定义函数（MySQL 8.0.16+适用） #

MySQL 8.0.16及以上版本正式支持CHECK约束校验，可通过自定义函数判断值是否合法，再绑定到字段约束上：

1. 创建校验函数

DELIMITER //
CREATE FUNCTION check_item_valid(item_val VARCHAR(50)) 
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE in_service INT;
    DECLARE in_product INT;
    SELECT COUNT(*) INTO in_service FROM services WHERE service_id = item_val;
    SELECT COUNT(*) INTO in_product FROM products WHERE product_id = item_val;
    RETURN in_service + in_product = 1;
END //
DELIMITER ;

2. 给orders表添加约束

ALTER TABLE orders
ADD CONSTRAINT chk_item1_valid CHECK (check_item_valid(item_1)),
ADD CONSTRAINT chk_item2_valid CHECK (check_item_valid(item_2));

方案2：调整表结构（推荐，更符合数据库设计规范） #

你当前的orders表用两个字段存同类型的item属于反范式设计，扩展性差，建议拆分为订单主表+订单明细表：

1. 新建订单明细表

CREATE TABLE order_items (
    order_id INT NOT NULL,
    item_type ENUM('service','product') NOT NULL COMMENT '物品类型：服务/商品',
    item_id VARCHAR(50) NOT NULL COMMENT '对应服务或商品的主键',
    sort TINYINT NOT NULL COMMENT '标识原表的item_1/sort=1，item_2/sort=2',
    PRIMARY KEY (order_id, sort),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
) ENGINE=InnoDB;

2. 删除orders表原有的item_1、item_2字段即可，后续关联查询就能拿到对应物品信息，也支持扩展更多物品数量。

方案3：触发器实现（兼容低版本MySQL） #

如果使用的是不支持CHECK约束的低版本MySQL，可通过插入、更新前的触发器做校验：

DELIMITER //
-- 插入前校验
CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NOT (EXISTS(SELECT 1 FROM services WHERE service_id = NEW.item_1) OR EXISTS(SELECT 1 FROM products WHERE product_id = NEW.item_1)) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'item_1取值不合法，必须来自services或products表主键';
    END IF;
    IF NOT (EXISTS(SELECT 1 FROM services WHERE service_id = NEW.item_2) OR EXISTS(SELECT 1 FROM products WHERE product_id = NEW.item_2)) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'item_2取值不合法，必须来自services或products表主键';
    END IF;
END //

-- 更新前校验
CREATE TRIGGER trg_orders_before_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    IF NOT (EXISTS(SELECT 1 FROM services WHERE service_id = NEW.item_1) OR EXISTS(SELECT 1 FROM products WHERE product_id = NEW.item_1)) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'item_1取值不合法，必须来自services或products表主键';
    END IF;
    IF NOT (EXISTS(SELECT 1 FROM services WHERE service_id = NEW.item_2) OR EXISTS(SELECT 1 FROM products WHERE product_id = NEW.item_2)) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'item_2取值不合法，必须来自services或products表主键';
    END IF;
END //
DELIMITER ;

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