SQL中外键多引用实现及重复列名错误解决咨询

首先，你碰到的ORA-00957: duplicate column name错误原因非常清晰——你的SQL语句里重复定义了STAFFNAME列三次：每次写STAFFNAME REFERENCES ...都是在尝试创建一个新的STAFFNAME列，而同一数据库表中绝对不允许出现重复列名。先把这个基础问题修正：先一次性定义所有列，再单独添加外键约束（另外从你的关系模型来看，staffNo已经作为外键关联了Staff_ChargeNurse，STAFFNAME大概率不需要重复设置外键，除非有特殊业务要求）。

接下来是你最关心的核心问题：如何让itemNo实现强制OR关系（即itemNo必须存在于Supplies_Pharmaceutical、Supplies_Surgical、Supplies_Non-Surgical这三个表中的任意一个）。标准SQL并不支持单个外键同时引用多个父表，所以我们可以通过以下几种方案来实现需求：

方案1：使用CHECK约束+自定义函数（Oracle专属支持） #

在Oracle中，你可以先创建一个函数来校验itemNo是否存在于三个表中的至少一个，再将这个函数绑定到CHECK约束上。

步骤1：创建校验函数 #

CREATE OR REPLACE FUNCTION is_item_valid(p_itemno CHAR(6)) RETURN BOOLEAN IS
    v_count NUMBER;
BEGIN
    -- 检查三个供应表中是否存在目标itemNo
    SELECT COUNT(*) INTO v_count
    FROM (
        SELECT itemNo FROM Supplies_Pharmaceutical WHERE itemNo = p_itemno
        UNION ALL
        SELECT itemNo FROM Supplies_Surgical WHERE itemNo = p_itemno
        UNION ALL
        SELECT itemNo FROM Supplies_Non-Surgical WHERE itemNo = p_itemno
    );
    RETURN v_count > 0;
END;
/

步骤2：创建表并添加约束 #

先修正列的定义，再添加外键和自定义校验约束：

CREATE TABLE REQUISITION (
    REQNO CHAR(6) CONSTRAINT REQNO_PK PRIMARY KEY,
    STAFFNO CHAR(6),
    STAFFNAME VARCHAR2(100), -- 仅定义一次列
    WARDNO CHAR(6),
    ITEMNO CHAR(6) NOT NULL,
    QUANTITY INT,
    DATEORDERED DATE,
    DATERECEIVED DATE, -- 注意你原拼写是DATERECIEVED，这里修正为正确拼写
    -- 绑定staffNo的外键约束
    CONSTRAINT REQ_STAFFNO_FK FOREIGN KEY (STAFFNO) REFERENCES STAFF_CHARGENURSE(STAFFNO),
    -- 添加itemNo有效性校验约束
    CONSTRAINT REQ_ITEMNO_VALID CHECK (is_item_valid(ITEMNO))
);

方案2：使用触发器实现验证 #

如果CHECK约束加函数的方式不符合你的需求，也可以用触发器在插入或更新数据时校验itemNo的有效性：

创建触发器 #

CREATE OR REPLACE TRIGGER trg_requisition_itemno_valid
BEFORE INSERT OR UPDATE OF ITEMNO ON REQUISITION
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM (
        SELECT itemNo FROM Supplies_Pharmaceutical WHERE itemNo = :NEW.ITEMNO
        UNION ALL
        SELECT itemNo FROM Supplies_Surgical WHERE itemNo = :NEW.ITEMNO
        UNION ALL
        SELECT itemNo FROM Supplies_Non-Surgical WHERE itemNo = :NEW.ITEMNO
    );
    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'ItemNo ' || :NEW.ITEMNO || ' 不存在于任何供应表中');
    END IF;
END;
/

表的创建方式和方案1一致，不需要额外添加CHECK约束。

方案3：重构表结构（更符合关系模型规范） #

从长期维护的角度来看，更推荐的方式是创建一个统一的Supplies主表，让三个供应表作为子表关联主表：

步骤1：创建主Supplies表 #

CREATE TABLE Supplies (
    ITEMNO CHAR(6) PRIMARY KEY,
    SUPPLY_TYPE VARCHAR2(20) NOT NULL CHECK (SUPPLY_TYPE IN ('PHARMACEUTICAL', 'SURGICAL', 'NON-SURGICAL')),
    -- 在这里添加所有供应品的通用字段
    ...
);

步骤2：创建子表并关联主表 #

-- 药品供应子表
CREATE TABLE Supplies_Pharmaceutical (
    ITEMNO CHAR(6) PRIMARY KEY REFERENCES Supplies(ITEMNO),
    -- 添加药品专属字段
    ...
);

-- 手术用品子表
CREATE TABLE Supplies_Surgical (
    ITEMNO CHAR(6) PRIMARY KEY REFERENCES Supplies(ITEMNO),
    -- 添加手术用品专属字段
    ...
);

-- 非手术用品子表
CREATE TABLE Supplies_Non-Surgical (
    ITEMNO CHAR(6) PRIMARY KEY REFERENCES Supplies(ITEMNO),
    -- 添加非手术用品专属字段
    ...
);

步骤3：创建Requisition表 #

现在只需要让itemNo关联主Supplies表即可，天然实现了OR关系（因为所有有效itemNo都在主表中）：

CREATE TABLE REQUISITION (
    REQNO CHAR(6) CONSTRAINT REQNO_PK PRIMARY KEY,
    STAFFNO CHAR(6) CONSTRAINT REQ_STAFFNO_FK REFERENCES STAFF_CHARGENURSE(STAFFNO),
    STAFFNAME VARCHAR2(100),
    WARDNO CHAR(6),
    ITEMNO CHAR(6) NOT NULL CONSTRAINT REQ_ITEMNO_FK REFERENCES Supplies(ITEMNO),
    QUANTITY INT,
    DATEORDERED DATE,
    DATERECEIVED DATE
);

这种方式更符合数据库设计的规范化原则，避免了复杂的约束或触发器，后续维护也会更简单。

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