如何让数据库表列仅接受两个不同表的外键其一作为输入？

你的推测完全正确：给resolved_by列添加两个独立外键后，Oracle会要求该列的值同时存在于Analyst和Specialist两个表中，而你需要的是“存在于其中任意一个”的逻辑，这就导致插入仅在单个表中存在的ID时触发约束错误。

下面提供两种可行的解决方法：

方法一：检查约束+自定义验证函数 #

通过自定义函数判断ID是否存在于任意一个父表，再用检查约束强制执行这个逻辑：

1. 先删除冲突的原有外键约束

-- 替换成你实际的外键约束名，可通过查询USER_CONSTRAINTS表获取
ALTER TABLE TICKET DROP CONSTRAINT SYS_C00234833;
ALTER TABLE TICKET DROP CONSTRAINT [第二个外键约束名];

2. 创建验证函数

CREATE OR REPLACE FUNCTION validate_resolver(p_id CHAR(6)) RETURN BOOLEAN IS
    v_match_count NUMBER;
BEGIN
    -- 统计ID在Analyst或Specialist中的出现次数
    SELECT COUNT(*)
    INTO v_match_count
    FROM (SELECT Id_Analyst AS resolver_id FROM Analyst
          UNION ALL
          SELECT ID_SPEACIALIST AS resolver_id FROM Specialist)
    WHERE resolver_id = p_id;
    
    -- 存在则返回TRUE，否则FALSE
    RETURN v_match_count > 0;
END;
/

3. 添加检查约束

ALTER TABLE TICKET
ADD CONSTRAINT chk_resolved_by_valid
CHECK (validate_resolver(resolved_by) OR resolved_by IS NULL);

这个约束允许resolved_by为NULL（未解决状态），或者值存在于任意一个父表中。

方法二：创建统一的Resolver父表（更符合范式） #

这种方式通过建立一个包含所有可处理工单人员的统一表，让Ticket的外键指向这个表，同时用触发器同步Analyst和Specialist的数据：

1. 创建统一的Resolver表

CREATE TABLE Resolver (
    Resolver_Id CHAR(6) PRIMARY KEY,
    Resolver_Type VARCHAR2(20) NOT NULL 
    CHECK (Resolver_Type IN ('ANALYST', 'SPECIALIST')) -- 标记人员类型
);

2. 给Analyst表添加同步触发器

CREATE OR REPLACE TRIGGER trg_sync_analyst_to_resolver
AFTER INSERT OR UPDATE OR DELETE ON Analyst
FOR EACH ROW
BEGIN
    IF INSERTING OR UPDATING THEN
        -- 用MERGE实现新增或更新
        MERGE INTO Resolver r
        USING DUAL ON (r.Resolver_Id = :NEW.Id_Analyst)
        WHEN NOT MATCHED THEN
            INSERT (Resolver_Id, Resolver_Type) VALUES (:NEW.Id_Analyst, 'ANALYST')
        WHEN MATCHED THEN
            UPDATE SET Resolver_Type = 'ANALYST';
    ELSIF DELETING THEN
        -- 删除Analyst记录时同步删除Resolver中的对应条目
        DELETE FROM Resolver WHERE Resolver_Id = :OLD.Id_Analyst;
    END IF;
END;
/

3. 给Specialist表添加同步触发器

CREATE OR REPLACE TRIGGER trg_sync_specialist_to_resolver
AFTER INSERT OR UPDATE OR DELETE ON Specialist
FOR EACH ROW
BEGIN
    IF INSERTING OR UPDATING THEN
        MERGE INTO Resolver r
        USING DUAL ON (r.Resolver_Id = :NEW.ID_SPEACIALIST)
        WHEN NOT MATCHED THEN
            INSERT (Resolver_Id, Resolver_Type) VALUES (:NEW.ID_SPEACIALIST, 'SPECIALIST')
        WHEN MATCHED THEN
            UPDATE SET Resolver_Type = 'SPECIALIST';
    ELSIF DELETING THEN
        DELETE FROM Resolver WHERE Resolver_Id = :OLD.ID_SPEACIALIST;
    END IF;
END;
/

4. 替换Ticket表的外键

ALTER TABLE TICKET DROP CONSTRAINT SYS_C00234833;
ALTER TABLE TICKET DROP CONSTRAINT [第二个外键约束名];

ALTER TABLE TICKET
ADD FOREIGN KEY (resolved_by) REFERENCES Resolver(Resolver_Id);

这种方法的优势是扩展性强，后续新增其他角色（如工程师）时，只需要新增对应表和同步触发器即可，无需修改Ticket表结构。

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