如何在数据库中添加基于当前时间的条件约束，限制患者重复预约未来时段

核心需求明确：同一患者不能为同一位医生预约多个未来时段的诊疗，已过期的过往预约数量没有限制，必须在数据库层面强制该规则。以下是针对主流数据库的具体实现方案：

1. PostgreSQL 最优实现：部分唯一索引 #

PostgreSQL原生支持部分唯一索引，可以直接对满足条件的记录施加唯一性约束，性能最优且无需额外字段：

CREATE UNIQUE INDEX idx_patient_doctor_future_appt
ON Appointment (patient_id, doctor_id)
WHERE scheduled_to > CURRENT_TIMESTAMP;

这个索引仅对scheduled_to晚于当前时间的预约生效，确保同一患者-医生组合下最多存在一条未来预约，过往过期预约不受任何限制。

2. MySQL 实现方案 #

MySQL 8.0.13及以上版本支持带条件的部分索引，语法和PostgreSQL类似：

CREATE UNIQUE INDEX idx_patient_doctor_future_appt
ON Appointment (patient_id, doctor_id)
WHERE scheduled_to > NOW();

如果是MySQL 5.7等低版本，可以通过生成列+联合唯一索引间接实现：

-- 添加存储生成列，标记是否为未来预约
ALTER TABLE Appointment
ADD COLUMN is_future BOOLEAN AS (scheduled_to > NOW()) STORED;

-- 创建联合唯一索引，仅约束未来预约
CREATE UNIQUE INDEX idx_patient_doctor_future_appt
ON Appointment (patient_id, doctor_id, is_future)
WHERE is_future = 1;

3. 通用触发器方案（适用于所有数据库） #

如果你的数据库不支持部分唯一索引（比如旧版SQL Server），可以用触发器在数据写入前做检查：
以SQL Server为例，触发器代码如下（其他数据库仅需调整语法细节）：

CREATE TRIGGER trg_check_future_appt_unique
ON Appointment
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 检查插入/更新的未来预约是否违反约束
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN Appointment a ON a.patient_id = i.patient_id AND a.doctor_id = i.doctor_id
        WHERE i.scheduled_to > GETDATE()
          AND a.scheduled_to > GETDATE()
          -- 排除更新操作中自身的旧记录
          AND NOT (a.patient_id = i.patient_id AND a.doctor_id = i.doctor_id AND a.scheduled_to = i.scheduled_to)
    )
    BEGIN
        RAISERROR('同一患者无法为同一位医生预约多个未来时段', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- 执行正常的插入/更新操作
    INSERT INTO Appointment (patient_id, doctor_id, scheduled_to)
    SELECT patient_id, doctor_id, scheduled_to FROM inserted;
END

触发器会在插入或更新预约时，检查当前操作的未来预约是否与已存在的未来预约冲突，若冲突则抛出错误并回滚操作。

方案对比 #

• 部分唯一索引：数据库原生支持，性能最高，维护成本低，优先选择。
• 生成列+联合索引：兼容性好，适合MySQL低版本，仅需额外一个字段。
• 触发器：兼容性最强，但性能略逊于索引方案，且需要维护触发器代码。

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