MySQL中如何引用员工特化表的emp_number作为外键？

方案一：改用物理化主表替代视图（推荐） #

把原来的employee视图改成实际的物理表，存储所有员工的通用属性（比如emp_number、name、hire_date等），然后让三个特化表（maid、driver、security_guard）通过emp_number外键关联到这个主表。这样emp_phone的外键就能合法指向物理表的emp_number。

示例SQL：

-- 创建物理化员工主表
CREATE TABLE employee (
    emp_number INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL
    -- 其他通用属性
);

-- 修改特化表，添加外键关联主表
CREATE TABLE maid (
    emp_number INT PRIMARY KEY,
    cleaning_skill VARCHAR(30),
    FOREIGN KEY (emp_number) REFERENCES employee(emp_number)
);

CREATE TABLE driver (
    emp_number INT PRIMARY KEY,
    license_number VARCHAR(20),
    FOREIGN KEY (emp_number) REFERENCES employee(emp_number)
);

CREATE TABLE security_guard (
    emp_number INT PRIMARY KEY,
    certification VARCHAR(30),
    FOREIGN KEY (emp_number) REFERENCES employee(emp_number)
);

-- 创建员工电话表，外键指向物理化employee表
CREATE TABLE emp_phone (
    emp_number INT NOT NULL,
    emp_phone VARCHAR(20) NOT NULL,
    PRIMARY KEY (emp_number, emp_phone),
    FOREIGN KEY (emp_number) REFERENCES employee(emp_number)
);

这种方式符合数据库设计规范，数据一致性有保障，后续维护也更方便。

方案二：为每个特化表单独创建电话表 #

给三类员工分别建对应的电话表，每个电话表的外键直接关联对应的特化表，再通过视图统一查询所有员工的电话数据。

示例SQL：

-- 女佣电话表
CREATE TABLE maid_phone (
    emp_number INT NOT NULL,
    emp_phone VARCHAR(20) NOT NULL,
    PRIMARY KEY (emp_number, emp_phone),
    FOREIGN KEY (emp_number) REFERENCES maid(emp_number)
);

-- 司机电话表
CREATE TABLE driver_phone (
    emp_number INT NOT NULL,
    emp_phone VARCHAR(20) NOT NULL,
    PRIMARY KEY (emp_number, emp_phone),
    FOREIGN KEY (emp_number) REFERENCES driver(emp_number)
);

-- 保安电话表
CREATE TABLE security_guard_phone (
    emp_number INT NOT NULL,
    emp_phone VARCHAR(20) NOT NULL,
    PRIMARY KEY (emp_number, emp_phone),
    FOREIGN KEY (emp_number) REFERENCES security_guard(emp_number)
);

-- 创建统一的电话视图
CREATE VIEW emp_phone AS
SELECT emp_number, emp_phone FROM maid_phone
UNION ALL
SELECT emp_number, emp_phone FROM driver_phone
UNION ALL
SELECT emp_number, emp_phone FROM security_guard_phone;

这个方案适合不想改动原有特化表结构的场景，但查询电话数据需要通过视图，且数据分散在三个表中。

方案三：触发器+约束模拟外键（应急备选） #

如果暂时不能修改现有表结构，可以在emp_phone表上创建触发器，在插入或更新时验证emp_number是否存在于employee视图中，同时给emp_number添加非空约束。但这种方式无法像真正的外键那样自动保证数据一致性，只能做基础验证，不推荐长期使用。

示例SQL：

CREATE TABLE emp_phone (
    emp_number INT NOT NULL,
    emp_phone VARCHAR(20) NOT NULL,
    PRIMARY KEY (emp_number, emp_phone)
);

-- 创建插入触发器
DELIMITER //
CREATE TRIGGER check_emp_exists_insert
BEFORE INSERT ON emp_phone
FOR EACH ROW
BEGIN
    IF NOT EXISTS (SELECT 1 FROM employee WHERE emp_number = NEW.emp_number) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '员工编号不存在';
    END IF;
END //
DELIMITER ;

-- 创建更新触发器
DELIMITER //
CREATE TRIGGER check_emp_exists_update
BEFORE UPDATE ON emp_phone
FOR EACH ROW
BEGIN
    IF NOT EXISTS (SELECT 1 FROM employee WHERE emp_number = NEW.emp_number) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '员工编号不存在';
    END IF;
END //
DELIMITER ;

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