clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601421 fiddles created (48001 in the last week).

CREATE TABLE nodes ( node_id int not null primary key , node_name varchar(10) not null , unique (node_name, node_id) -- superkey );
 hidden batch(es)


CREATE TABLE parent_nodes ( node_id int not null primary key , parent_id int not null references nodes (node_id) , node_name varchar(10) not null -- denormalized attribute, maintained by trg , constraint fk_nodes foreign key (node_name, node_id) references nodes (node_name, node_id) , constraint ak_perent unique (parent_id, node_name) );
 hidden batch(es)


create trigger distribute_name before insert on parent_nodes referencing new as n for each row set n.node_name = (select node_name from nodes where node_id = n.node_id)
 hidden batch(es)


CREATE TABLE ancestor_nodes ( node_id int not null references nodes (node_id) , ancestor_id int not null references nodes (node_id) );
 hidden batch(es)


create trigger add_closure after insert on parent_nodes referencing new as n for each row begin insert into ancestor_nodes (node_id, ancestor_id) values (n.node_id, n.parent_id) union all select n.node_id, ancestor_id from ancestor_nodes where node_id = n.parent_id union all select node_id, n.parent_id from ancestor_nodes where ancestor_id = n.node_id union all select a.node_id, b.ancestor_id from ancestor_nodes a cross join ancestor_nodes b where a.ancestor_id = n.node_id and b.node_id = n.parent_id; -- end;
 hidden batch(es)


create trigger del_closure after delete on parent_nodes referencing old as o for each row begin delete from ancestor_nodes x where exists ( select * from ( values (o.node_id, o.parent_id) union all select o.node_id, ancestor_id from ancestor_nodes where node_id = o.parent_id union all select node_id, o.parent_id from ancestor_nodes where ancestor_id = o.node_id union all select a.node_id, b.ancestor_id from ancestor_nodes a cross join ancestor_nodes b where a.ancestor_id = o.node_id and b.node_id = o.parent_id ) as y (node_id, ancestor_id) where (x.node_id, x.ancestor_id) = (y.node_id, y.ancestor_id) ); -- end;
 hidden batch(es)


insert into nodes (node_id, node_name) values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
 hidden batch(es)


insert into parent_nodes (node_id, parent_id) values (5,4),(4,3),(3,1),(2,1);
 hidden batch(es)


select * from ancestor_nodes;
NODE_ID ANCESTOR_ID
5 4
5 3
4 3
5 1
4 1
3 1
2 1
 hidden batch(es)


insert into nodes (node_id, node_name) values (14,'b');
 hidden batch(es)


-- invalid since 14 and 2 are both named 'b' and share the same parent 1 insert into parent_nodes (node_id, parent_id) values (14,1);
[IBM][CLI Driver][DB2/LINUXX8664] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "2" constrains table "FIDDLE_MBFLCVJFBAMVJEOWYDNK.PARENT_NODES" from having duplicate values for the index key. SQLSTATE=23505 SQLCODE=-803
 hidden batch(es)


select * from ancestor_nodes;
NODE_ID ANCESTOR_ID
5 4
5 3
4 3
5 1
4 1
3 1
2 1
 hidden batch(es)


delete from parent_nodes where node_id = 4;
 hidden batch(es)


select * from ancestor_nodes;
NODE_ID ANCESTOR_ID
5 4
3 1
2 1
 hidden batch(es)