如何让单列外键引用多列？SQL Server与MySQL方案咨询

SQL Server 实现方案 #

方案1：CHECK约束 + 标量函数 #

直接外键无法关联两列的并集，我们可以通过自定义标量函数校验pieceCode是否存在于Pieces的definitiveCode或internalCode中，再通过CHECK约束调用该函数：

1. 创建校验函数：

CREATE FUNCTION dbo.IsValidPieceCode(@pieceCode VARCHAR(255))
RETURNS BIT
AS
BEGIN
    RETURN CASE
        WHEN EXISTS (
            SELECT 1 FROM Pieces
            WHERE definitiveCode = @pieceCode OR internalCode = @pieceCode
        ) THEN 1
        ELSE 0
    END
END

2. 给Workings表添加CHECK约束：

ALTER TABLE Workings
ADD CONSTRAINT CK_Workings_PieceCode_Valid
CHECK (dbo.IsValidPieceCode(pieceCode) = 1)

注意：该方案存在一定性能开销，每次插入/更新都会触发函数查询。建议给Pieces表的definitiveCode和internalCode分别创建单独索引，优化校验速度。

方案2：索引视图（高性能方案） #

通过创建包含所有有效序列号的索引视图，让Workings的外键直接关联视图，性能更优：

1. 创建合并两列的视图：

CREATE VIEW vw_ValidPieceCodes
WITH SCHEMABINDING
AS
SELECT definitiveCode AS PieceCode FROM dbo.Pieces
UNION
SELECT internalCode AS PieceCode FROM dbo.Pieces

2. 给视图添加唯一聚集索引（必须是聚集且唯一，才能作为外键目标）：

CREATE UNIQUE CLUSTERED INDEX IX_vw_ValidPieceCodes_PieceCode
ON vw_ValidPieceCodes(PieceCode)

3. 给Workings表添加外键约束：

ALTER TABLE Workings
ADD CONSTRAINT FK_Workings_vw_ValidPieceCodes
FOREIGN KEY (pieceCode) REFERENCES vw_ValidPieceCodes(PieceCode)

该方案会维护物理化的索引数据集，外键校验直接走索引查询，还可支持级联删除等扩展逻辑。

MySQL 实现方案 #

MySQL不支持基于视图创建外键，且早期版本（8.0.16之前）CHECK约束仅被解析不生效，即使8.0.16之后支持CHECK，也不允许在其中调用自定义函数。主要采用以下替代方案：

方案：触发器校验 #

通过INSERT/UPDATE触发器校验pieceCode的有效性，不符合则抛出错误：

1. 创建插入前触发器：

DELIMITER //
CREATE TRIGGER trg_Workings_ValidatePieceCode_BEFORE_INSERT
BEFORE INSERT ON Workings
FOR EACH ROW
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM Pieces
        WHERE definitiveCode = NEW.pieceCode OR internalCode = NEW.pieceCode
    ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid pieceCode: does not exist in Pieces.definitiveCode or internalCode';
    END IF;
END //
DELIMITER ;

2. 创建更新前触发器：

DELIMITER //
CREATE TRIGGER trg_Workings_ValidatePieceCode_BEFORE_UPDATE
BEFORE UPDATE ON Workings
FOR EACH ROW
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM Pieces
        WHERE definitiveCode = NEW.pieceCode OR internalCode = NEW.pieceCode
    ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid pieceCode: does not exist in Pieces.definitiveCode or internalCode';
    END IF;
END //
DELIMITER ;

内容的提问来源于stack exchange，提问作者Stefano Eusebio Bergo'