如何实现f_course事实表单用户同起止日期仅插入一行的INSERT/UPSERT

要满足同一person_number在同一start_date和end_date区间仅存一条f_course记录的需求，可从数据库约束和SQL操作逻辑两层面入手，结合维度表d_course的关联处理，具体实现如下：

一、添加唯一性约束（核心保障） #

先在事实表f_course上创建唯一约束，从数据库层面杜绝重复数据：

ALTER TABLE f_course 
ADD CONSTRAINT uc_person_course_period 
UNIQUE (person_number, start_date, end_date);

该约束会强制person_number + start_date + end_date的组合唯一，任何违反此规则的插入/更新都会直接报错，是数据一致性的基础保障。

二、INSERT操作实现（避免重复插入） #

关联d_course获取d_course_id，同时通过条件判断确保待插入的时段记录不存在：

INSERT INTO f_course (d_course_id, person_number, start_date, end_date)
SELECT dc.id, :person_number, dc.start_date, dc.end_date
FROM d_course dc
-- 此处可根据业务需求匹配d_course，比如按课程名称或ID匹配
WHERE dc.id = :target_d_course_id 
AND NOT EXISTS (
    SELECT 1 FROM f_course fc
    WHERE fc.person_number = :person_number
    AND fc.start_date = dc.start_date
    AND fc.end_date = dc.end_date
);

如果用户同一时段报名多门课，业务上需确定保留哪门课的记录（比如取时长最长的），可修改子查询逻辑：

INSERT INTO f_course (d_course_id, person_number, start_date, end_date)
SELECT dc.id, :person_number, dc.start_date, dc.end_date
FROM (
    -- 取同一时段内课时最长的课程
    SELECT id, start_date, end_date
    FROM d_course
    WHERE start_date = :target_start_date 
      AND end_date = :target_end_date
    ORDER BY hours DESC
    LIMIT 1
) dc
WHERE NOT EXISTS (
    SELECT 1 FROM f_course fc
    WHERE fc.person_number = :person_number
    AND fc.start_date = dc.start_date
    AND fc.end_date = dc.end_date
);

三、UPSERT操作实现（存在则更新，不存在则插入） #

不同数据库的UPSERT语法略有差异，以下是主流数据库的实现方式：

PostgreSQL（利用ON CONFLICT） #

INSERT INTO f_course (d_course_id, person_number, start_date, end_date)
SELECT dc.id, :person_number, dc.start_date, dc.end_date
FROM d_course dc
WHERE dc.id = :target_d_course_id
ON CONFLICT (person_number, start_date, end_date)
-- 冲突时更新d_course_id为最新匹配的课程ID，可根据业务调整更新字段
DO UPDATE SET d_course_id = EXCLUDED.d_course_id;

MySQL（利用ON DUPLICATE KEY UPDATE） #

INSERT INTO f_course (d_course_id, person_number, start_date, end_date)
SELECT dc.id, :person_number, dc.start_date, dc.end_date
FROM d_course dc
WHERE dc.id = :target_d_course_id
ON DUPLICATE KEY UPDATE d_course_id = VALUES(d_course_id);

SQL Server（利用MERGE语句） #

MERGE INTO f_course fc
USING (
    SELECT id, start_date, end_date 
    FROM d_course 
    WHERE id = :target_d_course_id
) dc 
ON fc.person_number = :person_number 
   AND fc.start_date = dc.start_date 
   AND fc.end_date = dc.end_date
WHEN MATCHED THEN
    UPDATE SET fc.d_course_id = dc.id
WHEN NOT MATCHED THEN
    INSERT (d_course_id, person_number, start_date, end_date)
    VALUES (dc.id, :person_number, dc.start_date, dc.end_date);

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