如何为visit表添加约束限制用户6个月内不可重复到访同一地点

核心问题说明 #

标准SQL的CHECK约束仅能验证当前行的字段值，无法查询同表中已存在的其他记录，因此直接将你的查询逻辑转为CHECK约束是不可行的。以下提供两种可行的实现方案：

方案一：触发器（通用兼容方案） #

通过触发器函数在插入前检查历史记录，若存在6个月内的重复到访则阻止插入。

1. 创建触发器函数

CREATE OR REPLACE FUNCTION code.check_duplicate_visit()
RETURNS TRIGGER AS $$
BEGIN
    -- 验证当前用户+地点是否在过去6个月内有到访记录
    IF EXISTS (
        SELECT 1
        FROM code.visit
        WHERE pk_user_id = NEW.pk_user_id
          AND pk_place_id = NEW.pk_place_id
          AND NEW.data >= CURRENT_DATE - INTERVAL '6 months'
    ) THEN
        RAISE EXCEPTION '用户 % 近6个月内已到访过地点 %', NEW.pk_user_id, NEW.pk_place_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到visit表

CREATE TRIGGER trigger_check_duplicate_visit
BEFORE INSERT ON code.visit
FOR EACH ROW
EXECUTE FUNCTION code.check_duplicate_visit();

方案二：部分唯一索引（PostgreSQL专属，性能更优） #

若使用PostgreSQL，可利用部分唯一索引直接实现约束，性能比触发器更高效，数据库会通过索引自动维护验证逻辑：

CREATE UNIQUE INDEX idx_unique_user_place_6months
ON code.visit (pk_user_id, pk_place_id)
WHERE data >= CURRENT_DATE - INTERVAL '6 months';

注：该索引仅对近6个月内的记录强制(pk_user_id, pk_place_id)唯一性，超过6个月的旧记录不会触发约束限制。

优化你的查询语句 #

你原有的查询可简化为更高效的日期范围判断（无需计算月份差）：

SELECT pk_place_id, pk_user_id, data
FROM code.visit
WHERE data >= CURRENT_DATE - INTERVAL '6 months';

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