Hello. I have the following tables:
--- create table cardant.items ( item_id char(16) for bit data not null primary key, item_name varchar(128) not null, item_count bigint not null, constraint check_natural_count check (item_count >= 0) ) create table cardant.locations ( location_id char(16) for bit data not null primary key, location_description varchar(256) not null ) create table cardant.item_locations ( item_location_id char(16) for bit data not null, item_location_item_id char(16) for bit data not null, item_location_count bigint not null, constraint location_exists foreign key (item_location_id) references cardant.locations (location_id), constraint item_exists foreign key (item_location_item_id) references cardant.items (item_id), constraint check_location_natural_count check (item_location_count >= 0) ) --- These are tables for an inventory system. Each row of the items table describes a collection of items, so an example row might be ("c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", "4.7Ω Resistor", 100). Each row of the locations table describes a storage location, so an example row might be ("a7e0ab099d18b104dd673b01325053ba", "Box 1A"). Each row of the item_locations table associates items with storage locations. The relationship is defined such that items may be split between storage locations, so: Given items: ("c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", "4.7Ω Resistor", 100) ... and storage locations: ("a7e0ab099d18b104dd673b01325053ba", "Box 1A") ("f45a88bb90d7cc1e492d26f7a34c3041", "Box 1B") We might have this in the item_locations table: ("a7e0ab099d18b104dd673b01325053ba", "c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", 50) ("f45a88bb90d7cc1e492d26f7a34c3041", "c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", 50) That would indicate that 50 of the resistors are in Box 1A and 50 are in Box 1B. Now what I want to do is enforce the invariant that: for item_id in items: sum (item_location_count) where item_location_item_id = item_id == item_count In other words, for a given item ID, the sum of the items in the storage locations should sum to the value given in the items table. We don't want to get into a situation where the items table says we have 100 resistors, but the items_locations table says that there are 100 of them in Box 1A and 100 of them in Box 1B! The problem I'm having is that expressing this goes beyond what can be expressed in a check constraint, which almost certainly means expressing it as one or more triggers. The further problem I'm then having is that it feels like there needs to be a trigger on both the items and item_locations tables, and neither statement or row triggers really seem to apply; by definition I'm going to have to update multiple rows in multiple tables each time to maintain the invariant. Is there some better way I could be doing this? Maybe even some other way I could be defining the tables... -- Mark Raynsford | https://www.io7m.com
pgpUGIgRVeSQh.pgp
Description: OpenPGP digital signature