deapp_constraints.sql

-- 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);