PostgreSQL中patients与doctors表多对多关联如何用SQL声明

你描述的是典型的多对多实体关联，在 PostgreSQL 中通过新增中间联结表的方式实现，完全可以满足你提出的所有规则，具体操作如下：

前提说明 #

假设你已经创建完成 patients 和 doctors 两张基础表，两张表的主键 idPatient、idDoctor 均为 INT 类型（如果实际是其他类型，对应修改中间表的字段类型即可）。

核心实现：创建中间关联表 #

CREATE TABLE patient_doctor (
    -- 关联患者表主键
    id_patient INT NOT NULL,
    -- 关联医生表主键
    id_doctor INT NOT NULL,
    -- 可按需扩展字段，比如关联时间、责任类型等
    associate_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- 联合主键，避免同一名患者和同一名医生重复绑定
    PRIMARY KEY (id_patient, id_doctor),

    -- 外键关联患者表，删除/更新患者时同步处理关联记录
    CONSTRAINT fk_patient_doctor_patient FOREIGN KEY (id_patient)
        REFERENCES patients (idPatient)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    -- 外键关联医生表，删除/更新医生时同步处理关联记录
    CONSTRAINT fk_patient_doctor_doctor FOREIGN KEY (id_doctor)
        REFERENCES doctors (idDoctor)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

规则匹配说明 #

• 支持一名患者关联多名医生：同一个 id_patient 可在中间表对应多条不同 id_doctor 的记录
• 支持一名医生关联多名患者：同一个 id_doctor 可在中间表对应多条不同 id_patient 的记录
• 支持无关联的实体存在：关联关系仅存储在中间表中，patients、doctors 基础表不需要新增任何关联字段，未出现在中间表的患者/医生天然就是未关联状态，无需额外配置

可选优化 #

如果你的查询场景经常需要「根据医生ID查所有关联的患者」，可以额外给 id_doctor 单独建索引，提升查询效率：

CREATE INDEX idx_patient_doctor_doctor_id ON patient_doctor (id_doctor);

注：联合主键 (id_patient, id_doctor) 已经自带联合索引，「根据患者ID查所有关联的医生」的查询已经可以直接走索引，不需要额外建索引。

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