postgres - 对列的总和的约束（无触发器） [英] postgres - constraint on sum of a column (without triggers)

问题描述

我想约束父实体的子实体的某个属性的总和到该父实体的某个属性。我想使用PostgreSQL而不使用触发器。下面是一个例子：

假设我们有一个有卷属性的箱子。我们想用更小的盒子填充它，它们有自己的卷属性。

我想到的想法是：

  CREATE TABLE crates（
 crate_id int NOT NULL，
 crate_volume int NOT NULL，
 crate_volume_used int NOT NULL DEFAULT 0，
 
 CONSTRAINT crates_pkey PRIMARY KEY（crate_id），
 
 CONSTRAINT ukey_for_fkey_ref_from_boxes 
 UNIQUE（crate_id，crate_volume，crate_volume_used），
 
 crate_volume_used_cannot_be_greater_than_crate_volume 
 CHECK（crate_volume_used< = crate_volume），
 
 CONSTRAINT crate_volume_must_be_positive CHECK（crate_volume> = 0）
）; 
 
 
 
 CREATE TABLE框（
 box_id int NOT NULL，
 box_volume int NOT NULL，
 
 crate_id int NOT NULL ，
 crate_volume int NOT NULL，
 crate_volume_used int NOT NULL，
 
 id_of_previous_box int，
 previous_sum_of_volumes_of_boxes int，
 current_sum_of_volumes_of_boxes int NOT NULL，
 
 id_of_next_box int，
 
 CONSTRAINT boxes_pkey PRIMARY KEY（box_id），
 
 CONSTRAINT box_volume_must_be_positive CHECK（box_volume> = 0），
 
约束crate_fkey外键（crate_id，crate_volume，crate_volume_used）
参考箱（crate_id，crate_volume，crate_volume_used）MATCH SIMPLE 
 ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED，
 
约束previous_box_self_ref_fkey外键（id_of_previous_box，previous_sum_of_volumes_of_boxes）
参考箱（box_id，current_sum_of_volumes_of_boxes）MATCH SIMPLE 
 ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED，
约束ukey_for_previous_box_self_ref_fkey UNIQUE（box_id，current_sum_of_volumes_of_boxes），
约束previous_box_self_ref_fkey_validity UNIQUE（crate_id，id_of_previous_box），
 
约束next_box_self_ref_fkey外键（id_of_next_box）
参考箱（box_id）MATCH SIMPLE 
 ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED，
约束next_box_self_ref_fkey_validity UNIQUE（crate_id，id_of_next_box），
 
约束self_ref_key_integrity CHECK（
（id_of_previous_box IS NULL和previous_sum_of_volumes_of_boxes IS NULL）或
（id_of_previous_box IS NOT NULL和previous_sum_of_volumes_of_boxes IS NOT NULL）
）
 
约束sum_of_volumes_of_boxes_check1 CHECK（current_sum_of_volumes_of_boxes< = crate_volume），
约束sum_of_volumes_of_boxes_check2 CHECK（
（previous_sum_of_volumes_of_boxes IS NULL和current_sum_of_volumes_of_boxes = box_volume）或
（previous_sum_of_volumes_of_boxes IS NOT NULL AND current_sum_of_volumes_of_boxes = box_volume + previous_sum_of_volumes_of_boxes）
）
 
约束crate_volume_used_check CHECK（
（id_of_next_box IS NULL和crate_volume_used = current_sum_of_volumes_of_boxes ）OR 
（id_of_next_box IS NOT NULL）
）
）; 
 
 CREATE UNIQUE INDEX single_first_box ON框（crate_id）WHERE id_of_previous_box IS NULL; 
 CREATE UNIQUE INDEX single_last_box ON框（crate_id）WHERE id_of_next_box IS NULL; 
  

我的问题是，如果这是一种方式，如果有一个更好，更优化等）方式这样做。

提前感谢。

解决方案

blockquote>

我的问题是如果有一个更好的（更容易混淆，更优化等）这样做。

$ b b

是的，有：在一个字，使用触发器...

不，不要介意，你不想使用一个。在这里使用触发器;

扩展我之前发布的评论和其他评论：

这等于写入一个约束触发器来验证 sum（boxes.volume）<= crate.volume 。它只是这样做非常，非常 bastardized方式（通过检查约束和唯一键和外键伪装成聚合函数），并在你的应用程序中做相关的计算。

您在避免使用真正触发器的唯一成就将是在路上的错误，当两个并发更新将尝试影响相同的箱子。所有这一切，以保持不必要的唯一索引和外键为代价。

当然，你会最终解决一些或所有这些问题，提炼你的实现进一步通过使外键延迟，添加锁，yada yada。但是最后，你基本上是在写一个非常低效的聚合函数。

所以使用触发器。在箱子中使用after触发器来维护current_volume列，并在箱子上使用简单的check（）约束来执行检查。

如果你需要更多的说服力，只要考虑你在创建的开销。真。冷静一下：不要使用触发器（如果是这样）在条件箱中维护一个卷列，而是保持不少于六个字段绝对没有超出你的约束的目的，和那么多无用的唯一索引和外键约束相关他们，我真的失去计数，当我试图枚举他们。并检查他们的约束，在那。这些东西在存储和写入性能方面都有所增加。

I want to constrain the sum of a certain attribute of child entities of a parent entity to a certain attribute of that parent entity. I want to do this using PostgreSQL and without using triggers. An example follows;

Assume we have a crate with a volume attribute. We want to fill it with smaller boxes, which have their own volume attributes. The sum of volumes of all boxes in the crate cannot be greater than the volume of the crate.

The idea i have in mind is something like:

CREATE TABLE crates (
  crate_id int NOT NULL,
  crate_volume int NOT NULL,
  crate_volume_used int NOT NULL DEFAULT 0,

  CONSTRAINT crates_pkey PRIMARY KEY (crate_id),

  CONSTRAINT ukey_for_fkey_ref_from_boxes 
    UNIQUE (crate_id, crate_volume, crate_volume_used),

  CONSTRAINT crate_volume_used_cannot_be_greater_than_crate_volume 
    CHECK (crate_volume_used <= crate_volume),

  CONSTRAINT crate_volume_must_be_positive CHECK (crate_volume >= 0)
);



CREATE TABLE boxes (
  box_id int NOT NULL,
  box_volume int NOT NULL,

  crate_id int NOT NULL,
  crate_volume int NOT NULL,
  crate_volume_used int NOT NULL,

  id_of_previous_box int,
  previous_sum_of_volumes_of_boxes int,
  current_sum_of_volumes_of_boxes int NOT NULL,

  id_of_next_box int,

  CONSTRAINT boxes_pkey PRIMARY KEY (box_id),

  CONSTRAINT box_volume_must_be_positive CHECK (box_volume >= 0),

  CONSTRAINT crate_fkey FOREIGN KEY (crate_id, crate_volume, crate_volume_used) 
    REFERENCES crates (crate_id, crate_volume, crate_volume_used) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,

  CONSTRAINT previous_box_self_ref_fkey FOREIGN KEY (id_of_previous_box, previous_sum_of_volumes_of_boxes) 
    REFERENCES boxes (box_id, current_sum_of_volumes_of_boxes) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT ukey_for_previous_box_self_ref_fkey UNIQUE (box_id, current_sum_of_volumes_of_boxes),
  CONSTRAINT previous_box_self_ref_fkey_validity UNIQUE (crate_id, id_of_previous_box),

  CONSTRAINT next_box_self_ref_fkey FOREIGN KEY (id_of_next_box) 
    REFERENCES boxes (box_id) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT next_box_self_ref_fkey_validity UNIQUE (crate_id, id_of_next_box),

  CONSTRAINT self_ref_key_integrity CHECK (
    (id_of_previous_box IS NULL AND previous_sum_of_volumes_of_boxes IS NULL) OR
    (id_of_previous_box IS NOT NULL AND previous_sum_of_volumes_of_boxes IS NOT NULL)
  ),

  CONSTRAINT sum_of_volumes_of_boxes_check1 CHECK (current_sum_of_volumes_of_boxes <= crate_volume),
  CONSTRAINT sum_of_volumes_of_boxes_check2 CHECK (
    (previous_sum_of_volumes_of_boxes IS NULL AND current_sum_of_volumes_of_boxes=box_volume) OR
    (previous_sum_of_volumes_of_boxes IS NOT NULL AND current_sum_of_volumes_of_boxes=box_volume+previous_sum_of_volumes_of_boxes)
  ),

  CONSTRAINT crate_volume_used_check CHECK (
    (id_of_next_box IS NULL AND crate_volume_used=current_sum_of_volumes_of_boxes) OR
    (id_of_next_box IS NOT NULL)
  )
);

CREATE UNIQUE INDEX single_first_box ON boxes (crate_id) WHERE id_of_previous_box IS NULL;
CREATE UNIQUE INDEX single_last_box ON boxes (crate_id) WHERE id_of_next_box IS NULL;

My questions is if this is a way of doing this and, if there is a better (less confusing, more optimized etc.) way of doing this. Or should i just stick to triggers?

Thanks in advance.

解决方案

My questions is if there is a better (less confusing, more optimized etc.) way of doing this.

Yes, there is: in a word, use a trigger…

No, never mind that you don't want to use one. Use a trigger here; no ifs, no buts.

Expanding on the comments I and others posted earlier:

What you're doing amounts to writing a constraint trigger that is verifying that sum(boxes.volume) <= crate.volume. It's just doing so in a very, very bastardized way (by having check constraints and unique keys and foreign keys masquerade as an aggregate function), and doing the relevant calculations within your app at that.

Your only achievement in avoiding to use a genuine trigger will be errors down the road when two concurrent updates will try to affect the same crate. All this, at the cost of maintaining unnecessary unique indexes and foreign keys.

Sure, you'll end up fixing some or all of these issues and refining your "implementation" further by making the foreign keys deferrable, adding locks, yada yada. But in the end, you're basically doing what amounts to writing a vastly inefficient aggregate function.

So use a trigger. Either maintain a current_volume column in crates using after triggers on boxes, and enforce a check using a simple check() constraint on crates. Or add constraint triggers on boxes, to enforce the check directly.

If you need more convincing, just consider at the overhead that you're creating. Really. Take a cold, hard look at it: instead of maintaining one volume column in crates using triggers (if even that), you're maintaining no less than six fields that serve absolutely no purpose beyond your constraint, and so many useless unique indexes and foreign keys constraints related to them that I genuinely lose count when I try to enumerate them. And check constraints on them, at that. This stuff all adds up in terms of storage and write performance.

这篇关于postgres - 对列的总和的约束（无触发器）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！