Re: [Treebase-devel] study_id vs submission_id issues

[youjun g. <you...@ya...>]

Dear All,

I am investigating the cause of cascade delete failed on submission 22.
There are two issues that confuse me.

1. Why we need table sub_taxonlabel, sub_treeblock and sub_matrix (a typical
many to many setup)? but the relationship between taxonlabel, treeblock and
matrix to submission is many to one. this three unnecessary bridge tables
can hurt the performance.

2. The deletion of a phylotree node
phylotreenode table have 3 foreign keys point to the same table parent_id,
child_id and sibling_id. To delete a node all those foreign key value which
point to the deleting node need be set to "null" first, otherwise the
postgresql will complain.

When delete all the node in a tree, TB code issued two lines of sql:

>  "update phylotree set rootNode_id = null where phylotree_id = :treeID";
>   "delete from phylotreenode where phylotree_id = :treeID";
>
This should not work unless the foreigns keys allow cascade delete.


YOujun