SQLite Forum

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;
```