studies which have a genetic_profile_link to themselves cannot be deleted

[sheridancbio]

several years ago #2470 introduced the table genetic_profile_link, which includes these foreign key constraints: FOREIGN KEY (REFERRING_GENETIC_PROFILE_ID ) REFERENCES genetic_profile (GENETIC_PROFILE_ID) ON DELETE CASCADE, FOREIGN KEY (REFERRED_GENETIC_PROFILE_ID ) REFERENCES genetic_profile (GENETIC_PROFILE_ID) ON DELETE NO ACTION ON UPDATE NO ACTION

In the msk database the study gbm_tcga contained a genetic profile link from one of its genetic profiles (referring_genetic_profile = gbm_tcga_rna_seq_v2_mrna_median_Zscores) to another one of its genetic profiles (referred genetic_profile = gbm_tcga_rna_seq_v2_mrna) with reference_type "STATISTIC".

When the msk importer removes or replaces a study in the database, it calls DaoCancerStudy.deleteCancerStudyByCascade(id), which does DELETE FROM cancer_study WHERE cancer_study_id = id, however this fails for the study gbm_tcga with this error message: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (cgds_gdac.genetic_profile_link, CONSTRAINT genetic_profile_link_ibfk_2 FOREIGN KEY (REFERRED_GENETIC_PROFILE_ID) REFERENCES genetic_profile (GENETIC_PROFILE_ID) ON DELETE NO A)

The problem is that the genetic_profile_link record cannot be removed according to the constraint which prevents the deletion of the referred_genetic_profile records, even though it allows the cascade of a delete to remove the referring_genetic_profile records. In this case, the study is both the referring and the referred genetic profile in the link.

Possibly we need to add special code to the dao classes which will "unlock" these kinds of reflexive links before relying on the delete cascade to delete the study.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.