SQL添加约束实现仅可插入下单日期有效商品的不同方式

需保证order_item表中插入的关联记录，对应订单的date必须在关联商品的date_from和date_to区间内，以下是不同实现方式：

方案1：CHECK约束 + 自定义校验函数（PostgreSQL原生支持） #

这是最简洁的数据库层约束方案，直接在表结构层面绑定校验规则：
首先创建校验函数：

CREATE OR REPLACE FUNCTION check_order_item_valid_date(p_order_id INT, p_item_id INT)
RETURNS BOOLEAN AS $$
DECLARE
    v_order_date DATE;
    v_item_from DATE;
    v_item_to DATE;
BEGIN
    SELECT "date" INTO v_order_date FROM "order" WHERE id = p_order_id;
    SELECT date_from, date_to INTO v_item_from, v_item_to FROM "item" WHERE id = p_item_id;
    RETURN v_order_date BETWEEN v_item_from AND v_item_to;
END;
$$ LANGUAGE plpgsql STABLE;

然后给order_item表添加CHECK约束：

ALTER TABLE "order_item"
ADD CONSTRAINT order_item_date_valid CHECK (check_order_item_valid_date("order", "item"));

优缺点：实现简单，规则直接绑定表结构不会被常规操作绕过；缺点是如果后续修改order的日期或者item的有效期区间，已存在的order_item记录不会自动触发校验，需要额外处理修改场景的约束。

方案2：触发器实现 #

适用于需要覆盖更多操作场景的情况，比如订单日期、商品有效期后续修改时也要校验历史关联记录：
首先创建触发器函数：

CREATE OR REPLACE FUNCTION order_item_date_trigger_func()
RETURNS TRIGGER AS $$
DECLARE
    v_order_date DATE;
    v_item_from DATE;
    v_item_to DATE;
BEGIN
    SELECT "date" INTO v_order_date FROM "order" WHERE id = NEW."order";
    SELECT date_from, date_to INTO v_item_from, v_item_to FROM "item" WHERE id = NEW."item";
    IF v_order_date NOT BETWEEN v_item_from AND v_item_to THEN
        RAISE EXCEPTION '订单日期%不在商品%的有效期[% , %]内', v_order_date, NEW."item", v_item_from, v_item_to;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然后绑定INSERT/UPDATE触发器到order_item表：

CREATE TRIGGER trg_order_item_check_date
BEFORE INSERT OR UPDATE OF "order", "item" ON "order_item"
FOR EACH ROW EXECUTE FUNCTION order_item_date_trigger_func();

如果需要覆盖订单日期修改、商品有效期修改的场景，还可以额外给order和item表添加对应触发器，校验关联的order_item是否符合规则。
优缺点：灵活度高，可以自定义错误提示，也可以扩展覆盖关联表字段修改的场景；缺点是触发器逻辑相对隐蔽，排查问题时容易被忽略。

方案3：冗余字段 + 复合外键 + CHECK约束 #

通过冗余字段避免每次校验查询关联表，同时用外键保证冗余字段的准确性：
第一步：给order和item表添加复合唯一约束，用于外键关联：

ALTER TABLE "order" ADD CONSTRAINT order_id_date_unique UNIQUE (id, "date");
ALTER TABLE "item" ADD CONSTRAINT item_id_dates_unique UNIQUE (id, date_from, date_to);

第二步：给order_item表添加冗余字段并绑定外键和校验规则：

ALTER TABLE "order_item"
ADD COLUMN order_date DATE NOT NULL,
ADD COLUMN item_date_from DATE NOT NULL,
ADD COLUMN item_date_to DATE NOT NULL,
ADD CONSTRAINT fk_order_item_order FOREIGN KEY ("order", order_date) REFERENCES "order"(id, "date"),
ADD CONSTRAINT fk_order_item_item FOREIGN KEY ("item", item_date_from, item_date_to) REFERENCES "item"(id, date_from, date_to),
ADD CONSTRAINT order_item_date_valid CHECK (order_date BETWEEN item_date_from AND item_date_to);

优缺点：校验不需要跨表查询，性能更好，冗余字段有外键保证不会和主表数据不一致；缺点是增加了冗余存储，插入order_item的时候需要额外传入三个冗余字段，修改order或item的日期字段时会因为外键约束无法直接修改，必须先处理关联的order_item记录，适合不允许修改历史订单日期、商品历史有效期的业务场景。

方案4：应用层校验 #

不在数据库层加约束，所有order_item的插入、更新操作都在业务代码中先查询订单和商品的日期做校验，通过后再执行数据库操作。
优缺点：实现成本最低，不需要修改数据库结构；缺点是可靠性差，只要有一处代码没加校验就会产生脏数据，也无法规避直接操作数据库的场景。

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