1

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.

10
  • FOREIGN KEY (id_of_previous_box, previous_sum_of_volumes_of_boxes) REFERENCES boxes (box_id, current_sum_of_volumes_of_boxes), is not allowed. A foreign key can only refer to the PK of the other table(which is box_id). Commented Dec 23, 2013 at 14:09
  • @joop: Not quite: "A foreign key must reference columns that either are a primary key or form a unique constraint.". A unique constraint is sufficient. Commented Dec 23, 2013 at 14:14
  • "and without using triggers" — not possible. (Unless you count enforcing the constraint in your app as not being a trigger, even though it'll in fact just be a poorly designed one.) Commented Dec 23, 2013 at 14:47
  • @Denis: what is it that is "not possible"? is it not possible to achieve what is explained in the OP in a way that is better than shown in the OP, or is it not possible to achieve it with the method explained in the OP? (without using triggers) Commented Dec 23, 2013 at 15:15
  • 1
    It's not clear why the "sum_of_volumes_..." columns and the constraints themselves are in the boxes tables rather than the crates table. More generally a db schema alone can't solve the problem of "aggregate" constraints across rows. You'd need to include the pseudo-code with serialization and locking strategies Commented Dec 23, 2013 at 15:41

1 Answer 1

3

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.

Sign up to request clarification or add additional context in comments.

3 Comments

Thank you Denis, for all your time to reply and valuable insights. This, I think, is not an answer for the question (it is more like asking not to ask such a question) but valuable indeed. There is especially one part I really would like to be better informed about - "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" statement. Could you please elaborate? Thanks again.
You don't even need concurrent transactions to generate errors in your setup. One of these probably will: a) update, in a single query, every box in a crate. b) update, in a single query, one box so that it takes the place of another in a crate and vice-versa. c) update, in a single query, the first two boxes in a crate so as to switch their position. The list could likely go on ad nausea. Per my answer: yes, you can probably fix most/all of them by adding or changing constraints and such; but in the end, the simplest and fastest option will be to simply use triggers.
I have edited the question to make your reply the correct answer (which as far as i can see is one). After some tinkering, I see know that it is much more harder to manage such kind of a structure than managing "having triggers". I will not delete the first comment, which includes the question you are answering in your comment before this one. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.