Re: [Treebase-devel] Some data problems

[Hilmar L. <hl...@ne...>]

On Jan 28, 2010, at 11:28 PM, William Piel wrote:

> SELECT DISTINCT tls.study_id
> FROM taxonlabel tl JOIN taxonlabelset_taxonlabel tlstl ON  
> (tl.taxonlabel_id = tlstl.taxonlabel_id)
> JOIN taxonlabelset tls ON (tls.taxonlabelset_id =  
> tlstl.taxonlabelset_id)
> WHERE tl.study_id <> tls.study_id
> ORDER BY tls.study_id
>
> result: 22, 2264.
>
> So, only 2 studies. 22 was used for testing; 2264 is missing (study  
> doesn't exist).
>
> Now I'll do the same, but this time by way of the matrix:
>
> SELECT DISTINCT mx.study_id
> FROM taxonlabel tl JOIN matrixrow mxr ON (tl.taxonlabel_id =  
> mxr.taxonlabel_id)
> JOIN matrix mx ON (mxr.matrix_id = mx.matrix_id)
> WHERE tl.study_id <> mx.study_id
> ORDER BY mx.study_id
>
> result: 2369, 2374
>
> Again, two studies_ids for missing studies.


Does this mean that there are dangling foreign keys? Are the foreign  
key constraints not enforced by the database?

I guess that need not necessarily be resolved before the release, but  
it seems to me that there is a major data cleanup effort necessary at  
some point, combined with enforcing all possible constraints in the  
database.

	-hilmar
-- 
===========================================================
: Hilmar Lapp  -:- Durham, NC -:- informatics.nescent.org :
===========================================================