closure table, prevent identical childrens names per parent
How do I prevent that, in a closure table, a parent has no children with duplicate 'names'?
I think I need a trigger for that, but cannot get it to work and I'm not sure I'm on the right path with this. Some guidance is welcome.
PRAGMA writable_schema = ON; INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql) VALUES('table','asset_closure','asset_closure',0,'CREATE VIRTUAL TABLE IF NOT EXISTS asset_closure USING transitive_closure ( tablename="asset_tree", idcolumn="id", parentcolumn="parent_id")'); PRAGMA writable_schema = OFF; CREATE TABLE IF NOT EXISTS asset_tree( id INTEGER PRIMARY KEY NOT NULL, parent_id INTEGER REFERENCES asset_tree(id) CHECK(id <> parent_id), CONSTRAINT u_id_parent UNIQUE(id, parent_id) ); CREATE TABLE IF NOT EXISTS asset_node( node_id REFERENCES asset_tree(id), node TEXT ); CREATE INDEX idx_parent_id ON asset_tree(parent_id); CREATE INDEX idx_node_id ON asset_node(node_id); CREATE INDEX idx_node ON asset_node(node); -- trigger to prevent a parent to have children with teh same 'name' CREATE TRIGGER no_duplicate_childname_per_parent BEFORE INSERT ON asset_node WHEN asset_node.node.old IN ( --selects the parents childrens names (asset_node.node) --where parent_id = last_insert_rowid() --temp table SELECT asset_node.node AS child FROM asset_tree JOIN asset_node ON asset_tree.id = asset_node.node_id WHERE asset_tree.parent_id = ( SELECT parent_id FROM asset_tree WHERE id = last_insert_rowid() ) ) BEGIN SELECT(RAISE (FAIL, 'A parent can not have two children with the same name')); END; -- Insert a single node PRAGMA temp_store = 2; BEGIN TRANSACTION; CREATE TEMP TABLE _last_row( lr INTEGER, value INTEGER ); INSERT INTO asset_tree (parent_id) VALUES (NULL) --null for root items, else parent_id ; INSERT INTO _last_row VALUES (1, last_insert_rowid()) ; INSERT INTO asset_node(node_id, node) VALUES ((SELECT value FROM _last_row WHERE lr = 1), 'new inserted asset') ; DROP TABLE _last_row; END TRANSACTION;
CREATE TRIGGER no_duplicate_childname_per_parent BEFORE INSERT ON asset_node BEGIN SELECT RAISE(FAIL, 'A parent cannot have two children with the same name') FROM asset_node AS sibling_node JOIN asset_tree AS sibling_tree ON sibling_node.node_id = sibling_tree.id JOIN asset_tree AS parent_tree ON sibling_tree.parent_id = parent_tree.id JOIN asset_tree AS new_tree ON new_tree.parent_id = parent_tree.id WHERE new_tree.id = NEW.node_id AND sibling_node.node = NEW.node; END;
I'm sure you've done your own data modeling, however, I've come across several instances in life where parents have children with duplicate names. Unusual perhaps, but it worked for them
Wait … this is real life genealogy data, not just an abstraction ? In that case you must allow duplicate names within families. It happens all the time. One reason for it happening is that the real names are all foreign but get translated to the same English name. For instance, 'Smuel' and 'Sami' (which should both be written in non-Roman characters) both get translated into the English 'Samuel'.
There are two normal ways of solving the problem. They both involve adding an extra integer used for disambiguation. One is to just use an incrementing integer, so Smuel ends up as 'Samuel (1)' and 'Sami ends up as 'Samuel (2)'. The other is instead of small integers to use their year of birth, giving 'Samuel (1833)' and 'Samuel (1835)'.
The former being always the better option.
The latter will be hard for them Smuel and Sami twins.
Also, to add to Simon's suggestion, never make a hard rule in a Database structure if it is not a hard rule (or at least an enforceable one) in real life.
It's an abstraction, not genealogy. It's about raw materials and (intermediate) products that are the leafs. In packing.carton.6_bottles (index 3.18.2) and packing.carton.12_bottles (index 3.18.5) the leaves have to be unique. The names can appear in other branches. Even packing.carton.6_bottles.6_bottles (index 220.127.116.11)could exist. packing.carton.6_bottles (index 3.22.2) should not be possible