按ER图实现FK约束时遇到的两个数据库设计问题求助

问题1：Doctor表关联Patient和Employee的主键冲突解决 #

当两个父表的主键同名，但需要在子表中分别作为外键时，核心思路是给子表的外键列分配语义化的唯一名称，避免列名冲突，再分别创建外键约束关联对应父表的主键。

示例SQL：

-- 创建Patient表
CREATE TABLE Patient (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    -- 其他业务字段，如姓名、年龄等
);

-- 创建Employee表
CREATE TABLE Employee (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    -- 其他业务字段，如姓名、职位等
);

-- 创建Doctor表，使用区分度高的外键列名
CREATE TABLE Doctor (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT, -- 关联Patient表的主键ID
    employee_id INT, -- 关联Employee表的主键ID
    -- 其他业务字段，如科室、职称等
    -- 定义外键约束
    FOREIGN KEY (patient_id) REFERENCES Patient(ID) ON DELETE SET NULL,
    FOREIGN KEY (employee_id) REFERENCES Employee(ID) ON DELETE SET NULL
);

这里用patient_id和employee_id明确区分两个外键列，分别关联对应父表的主键。可根据业务需求调整外键的级联规则（比如ON DELETE CASCADE或ON UPDATE RESTRICT）。

问题2：Employee表自关联设计 #

自关联用于表示表内的层级关系（比如员工-直属上级），实现方式是在表内添加一个外键列，关联自身的主键，同时允许该列值为NULL（对应无上级的顶级员工）。

示例SQL：

CREATE TABLE Employee (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    position VARCHAR(50),
    manager_id INT, -- 存储当前员工的直属上级ID
    -- 其他业务字段，如入职日期、薪资等
    -- 定义自关联外键约束
    FOREIGN KEY (manager_id) REFERENCES Employee(ID) ON DELETE SET NULL
);

• manager_id列存储上级员工的ID，顶级管理者的该列值设为NULL即可
• 级联规则ON DELETE SET NULL表示上级员工被删除时，下属的manager_id自动置空；如果业务不允许下属脱离上级，也可使用ON DELETE RESTRICT阻止删除操作

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