SQLite Forum

closure table, prevent identical childrens names per parent
Login

closure table, prevent identical childrens names per parent

(1) By ingo on 2020-06-12 12:35:55 [link] [source]

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.

TIA,

Ingo

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;

(2) By Clemens Ladisch (cladisch) on 2020-06-12 13:47:38 in reply to 1 [source]

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;

(3) By ingo on 2020-06-12 21:28:14 in reply to 2 [link] [source]

I will study more, Thank you.

Ingo

(4) By anonymous on 2020-06-13 06:39:42 in reply to 1 [link] [source]

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

(5) By Simon Slavin (slavin) on 2020-06-13 12:41:10 in reply to 4 [link] [source]

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)'.

(6) By Ryan Smith (cuz) on 2020-06-13 16:39:38 in reply to 5 [link] [source]

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.

(7) By ingo on 2020-06-14 06:00:08 in reply to 5 [link] [source]

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 3.18.2.27)could exist. packing.carton.6_bottles (index 3.22.2) should not be possible