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.
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).boxestables rather than thecratestable. 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