Last active
August 29, 2015 14:17
-
-
Save forus/cc1da63debb81b6ecfd2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Subject Sample Mapping (aka Assay) table | |
alter table deapp.de_subject_sample_mapping add constraint de_subject_sample_mapping_pk primary key (assay_id); | |
alter table deapp.de_subject_sample_mapping add constraint de_subject_sample_mapping_patient_id_fk foreign key (patient_id) references i2b2demodata.patient_dimension(patient_num) on delete cascade; | |
alter table deapp.de_subject_sample_mapping add constraint de_subject_sample_mapping_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform); | |
alter table deapp.de_subject_sample_mapping alter concept_code type character varying(50); | |
alter table deapp.de_subject_sample_mapping add constraint de_subject_sample_mapping_concept_code_fk foreign key (concept_code) references i2b2demodata.concept_dimension(concept_cd) on delete cascade; | |
-- HD data tables | |
-- Add PKs for data tables that miss ones | |
alter table deapp.de_subject_microarray_data add constraint de_subject_microarray_data_pk primary key (probeset_id, assay_id); | |
alter table deapp.de_subject_metabolomics_data add constraint de_subject_metabolomics_data_pk primary key (metabolite_annotation_id, assay_id); | |
alter table deapp.de_subject_mirna_data add constraint de_subject_mirna_data_pk primary key (probeset_id, assay_id); | |
alter table deapp.de_subject_protein_data add constraint de_subject_protein_data_pk primary key (protein_annotation_id, assay_id); | |
alter table deapp.de_subject_rna_data add constraint de_subject_rna_data_pk primary key (probeset_id, assay_id); | |
-- FKs to SSM | |
alter table deapp.de_subject_metabolomics_data add constraint de_subject_metabolomics_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
alter table deapp.de_subject_acgh_data add constraint de_subject_acgh_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
alter table deapp.de_subject_microarray_data add constraint de_subject_microarray_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
alter table deapp.de_subject_mirna_data alter assay_id type bigint; | |
alter table deapp.de_subject_mirna_data add constraint de_subject_mirna_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
alter table deapp.de_subject_rbm_data add constraint de_subject_rbm_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
alter table deapp.de_subject_protein_data alter assay_id type bigint; | |
alter table deapp.de_subject_protein_data add constraint de_subject_protein_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
alter table deapp.de_subject_rna_data add constraint de_subject_rna_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
alter table deapp.de_variant_subject_summary add constraint de_variant_subject_summary_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
alter table deapp.de_subject_rnaseq_data add constraint de_subject_rnaseq_data_assay_id_fk foreign key (assay_id) references de_subject_sample_mapping(assay_id) on delete cascade; | |
-- Fix PKs for Annotation Table | |
drop trigger trg_de_mrna_annotation_id on deapp.de_mrna_annotation; | |
drop function deapp.tf_trg_de_mrna_annotation_id(); | |
alter table deapp.de_mrna_annotation drop constraint de_mrna_annotation_pkey; | |
alter table deapp.de_mrna_annotation drop column de_mrna_annotation_id; | |
alter table deapp.de_mrna_annotation add constraint de_mrna_annotation_pk primary key (probeset_id); | |
alter table deapp.de_qpcr_mirna_annotation add constraint de_qpcr_mirna_annotation_pk primary key (probeset_id); | |
alter table deapp.de_rnaseq_annotation add constraint de_rnaseq_annotation_pk primary key (transcript_id); | |
-- FKs to Annotation Table | |
alter table deapp.de_subject_microarray_data add constraint de_subject_microarray_data_probeset_id_fk foreign key (probeset_id) references de_mrna_annotation(probeset_id); | |
alter table deapp.de_subject_mirna_data add constraint de_subject_mirna_data_probeset_id_fk foreign key (probeset_id) references de_qpcr_mirna_annotation(probeset_id); | |
alter table deapp.de_subject_rna_data add constraint de_subject_rna_data_probeset_id_fk foreign key (probeset_id) references de_rnaseq_annotation(transcript_id); | |
-- FKs to Platform Definition | |
alter table deapp.de_metabolite_annotation add constraint de_metabolite_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade; | |
alter table deapp.de_mrna_annotation add constraint de_mrna_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade; | |
alter table deapp.de_qpcr_mirna_annotation add constraint de_qpcr_mirna_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade; | |
alter table deapp.de_rbm_annotation add constraint de_rbm_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade; | |
alter table deapp.de_protein_annotation add constraint de_protein_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade; | |
alter table deapp.de_rnaseq_annotation add constraint de_rnaseq_annotation_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform) on delete cascade; | |
alter table deapp.de_metabolite_super_pathways add constraint de_metabolite_super_pathways_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform); | |
alter table deapp.de_metabolite_sub_pathways add constraint de_metabolite_sub_pathways_gpl_id_fk foreign key (gpl_id) references de_gpl_info(platform); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment