postgresql - postgres - 对列总和的约束(无触发器)

Question

我想将父实体的子实体的某个属性的总和限制为该父实体的某个属性。我想在不使用触发器的情况下使用 PostgreSQL 来执行此操作。下面是一个例子；

假设我们有一个带有 volume 属性的箱子。我们想用更小的盒子填充它，这些盒子有自己的体积属性。 crate 中所有箱子的体积总和不能大于 crate 的体积。

我的想法是这样的:

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;

我的问题是，这是否是一种执行此操作的方法，以及是否有更好的(更少混淆、更优化等)方法来执行此操作。还是我应该坚持使用触发器？

提前致谢。

Answer 1

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

是的，有:总之，使用触发器……

不，没关系，你不想使用一个。在这里使用触发器；没有如果，没有但是。

扩展我和其他人之前发表的评论:

您所做的相当于编写一个约束触发器来验证 sum(boxes.volume) <= crate.volume .它只是以一种非常、非常 SCSS 的方式(通过检查约束、唯一键和外键伪装成聚合函数)，然后在您的应用程序中进行相关计算。

避免使用真正的触发器的唯一成就将是在两个并发更新试图影响同一个 crate 时出现错误。所有这一切，都是以维护不必要的唯一索引和外键为代价的。

当然，您最终会解决部分或所有这些问题，并通过使外键可延迟、添加锁来进一步完善您的“实现”，yada yada。但最终，您所做的基本上相当于编写一个效率极低的聚合函数。

所以使用触发器。在箱子上使用 after 触发器维护箱子中的 current_volume 列，并在箱子上使用简单的 check() 约束强制执行检查。或者在框上添加约束触发器，以直接执行检查。

如果您需要更有说服力，只需考虑您正在创建的开销。真的。冷静、认真地审视一下:不是使用触发器在 crate 中维护 一个 volume 列(如果是的话)，而是维护不少于 六个 服务的字段绝对没有超出您的约束的目的，以及与它们相关的许多无用的唯一索引和外键约束，以至于当我尝试枚举它们时我真的数不清了。然后检查对它们的约束。这些东西在存储和写入性能方面都加起来了。