sql - 当所有其他列都相等时，对具有不同标签的值的约束

Question

我想创建一个约束来验证在“stats_type”列中分类为“最小值”的值(来自“nominal_value”列)是否等于或小于分类为“平均值”的值，当所有值在其他列中是相等的。换句话说，给定相应的元组，除了“oid”、“stats_type”和“nominal_value”列，我想确保标记为“最小”的值始终等于或小于标记为“平均”的值。

很难解释，所以我做了下面的例子:

CREATE TABLE price (
    oid SERIAL  NOT NULL,
    product INTEGER  NOT NULL,
    territory INTEGER  NOT NULL,
    stats_type INTEGER  NOT NULL,
    year INTEGER  NOT NULL,
    nominal_value NUMERIC(6,2)  NOT NULL,
    data_source INTEGER  NOT NULL,
    CONSTRAINT pk_price PRIMARY KEY (oid),
    CONSTRAINT price_1 UNIQUE (product, territory, stats_type, year, data_source),
    CONSTRAINT price_2 CHECK (nominal_value > 0)
);

INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (1, 55, 5611, 1, 2014, 120, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (2, 55, 5611, 2, 2014, 160, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (3, 55, 5615, 1, 2014, 60, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (4, 55, 5611, 3, 2014, 180, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (5, 62, 5615, 1, 2013, 1500, 3);
INSERT INTO price (oid, product, territory, stats_type, year, nominal_value, data_source) VALUES (6, 62, 5615, 2, 2013, 1300, 3);

“stats_type”标签为:1 = 最小值，2 = 平均值，3 = 最大值。

看前两行；它们是相等的，除了“oid”、“stats_type”和“nominal_value”；所以我想验证:120(第一行标记为最小值)是否小于或等于 160(标记为平均值的值)？本例答案是肯定的，因此通过了验证。

现在，最后两行(第 5 行和第 6 行)也与“产品”、“地区”、“年份”和“数据源”列相关。然而，碰巧第 5 行的标称值 1500 不应小于第 6 行的 1300(因为 1500 应该是最小值，而 1300 是平均值)。

我该怎么做？是否可以使用“检查”约束来完成此类任务？

Answer 1

由于查询表的必要性，这不能使用检查约束来完成。您可以使用触发器:

create or replace function price_trigger()
returns trigger language plpgsql as $$
begin
    if exists (
        select 1
        from price p
        where (p.product, p.territory, p.year, p.data_source) = 
              (new.product, new.territory, new.year, new.data_source)
        and (
            p.stats_type < new.stats_type and p.nominal_value > new.nominal_value
            or
            p.stats_type > new.stats_type and p.nominal_value < new.nominal_value
            )
        )
    then
        raise exception 'Nominal value error';
    end if;
    return new;
end $$;

create trigger price_trigger
before insert or update on price
for each row
execute procedure price_trigger();

触发函数检查所有条件(min < avg < max)。