X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=schema%2Fhuman_9606_schema%2Fhuman_9606_constraint_postgresql.sql;h=92d597308de144e205b928b11b90792595981552;hb=77ec8a32a887e1eb6364f327f55049cae8e69f95;hp=38eca52e476c010678b117dc208b36c579bec465;hpb=ea7460b34b8123c023d3f73296a9cb5f7703abbe;p=dbsnp.git diff --git a/schema/human_9606_schema/human_9606_constraint_postgresql.sql b/schema/human_9606_schema/human_9606_constraint_postgresql.sql index 38eca52..92d5973 100644 --- a/schema/human_9606_schema/human_9606_constraint_postgresql.sql +++ b/schema/human_9606_schema/human_9606_constraint_postgresql.sql @@ -1,457 +1,72 @@ -ALTER TABLE AlleleFreqBySsPop ADD - CONSTRAINT pk_AlleleFreqBySsPop_b129 PRIMARY KEY - ( - subsnp_id, - pop_id, - allele_id - ) -; - -ALTER TABLE Batch ADD - CONSTRAINT pk_Batch PRIMARY KEY - ( - batch_id - ), ADD - CONSTRAINT ck_batch_type CHECK (batch_type='ANC' OR (batch_type='MNT' OR (batch_type='VAL' OR (batch_type='POP' OR (batch_type='IND' OR (batch_type='SNP' OR batch_type='NOV')))))) -; - -ALTER TABLE BatchCita ADD - CONSTRAINT pk_BatchCita PRIMARY KEY - ( - batch_id, - position - ) -; - -ALTER TABLE BatchCommLine ADD - CONSTRAINT pk_BatchCommLine PRIMARY KEY - ( - batch_id, - line_num - ) -; - -ALTER TABLE BatchCultivar ADD - CONSTRAINT pk_BatchCultivar PRIMARY KEY - ( - batch_id, - line_num - ) -; - -ALTER TABLE BatchMeExLine ADD - CONSTRAINT pk_BatchMeExLine PRIMARY KEY - ( - batch_id, - line_num - ) -; - -ALTER TABLE BatchStrain ADD - CONSTRAINT pk_BatchStrain PRIMARY KEY - ( - batch_id, - line_num - ) -; - -ALTER TABLE BatchValCode ADD - CONSTRAINT pk_BatchValCode PRIMARY KEY - ( - batch_id - ) -; - -ALTER TABLE Contact ADD - CONSTRAINT pk_Contact PRIMARY KEY - ( - batch_id, - handle - ) -; - -ALTER TABLE FreqSummaryBySsPop ADD - CONSTRAINT pk_FreqSummaryBySsPop PRIMARY KEY - ( - subsnp_id, - pop_id - ) -; - -ALTER TABLE GeneIdToName ADD - CONSTRAINT pk_GeneIdToName PRIMARY KEY - ( - gene_id - ) -; - -ALTER TABLE GtyFreqBySsPop ADD - CONSTRAINT pk_GtyFreqBySsPop_b129 PRIMARY KEY - ( - subsnp_id, - pop_id, - unigty_id - ) -; - -ALTER TABLE IndGrpCode ADD - CONSTRAINT pk_IndGrpCode PRIMARY KEY - ( - code - ) -; - -ALTER TABLE IndivBySource ADD - CONSTRAINT pk_IndivBySource PRIMARY KEY - ( - src_id, - src_ind_id - ) -; - - -ALTER TABLE Individual ADD - CONSTRAINT pk_Individual PRIMARY KEY - ( - ind_id - ) -; - - -ALTER TABLE Pedigree ADD - CONSTRAINT p_ped_id PRIMARY KEY - ( - ped_id - ) -; - -ALTER TABLE PedigreeIndividual ADD - CONSTRAINT pk_PedigreeIndividual PRIMARY KEY - ( - ped_id, - ind_id - ) -; - -ALTER TABLE PopLine ADD - CONSTRAINT pk_PopLine PRIMARY KEY - ( - pop_id, - line_num - ) -; - -ALTER TABLE PopMandLine ADD - CONSTRAINT pk_PopMandLine PRIMARY KEY - ( - pop_id, - line_num - ) -; - -ALTER TABLE Population ADD - CONSTRAINT pk_Population_pop_id PRIMARY KEY - ( - pop_id - ) -; - - - - -ALTER TABLE SNPAlleleFreq ADD - CONSTRAINT pk_SNPAlleleFreq PRIMARY KEY - ( - snp_id, - allele_id - ) -; - -ALTER TABLE SNPAncestralAllele ADD --- CONSTRAINT DF__SNPAncest__batch__664F5149 DEFAULT ((0)) FOR batch_id, - CONSTRAINT pk_SNPAncestralAllele PRIMARY KEY - ( - snp_id, - batch_id - ) -; - -ALTER TABLE SNPGtyFreq ADD - CONSTRAINT pk_SNPGtyFreq PRIMARY KEY - ( - snp_id, - unigty_id - ) -; - -ALTER TABLE SNPHWProb ADD - CONSTRAINT pk_SNPHWProb PRIMARY KEY - ( - snp_id - ) -; - -ALTER TABLE SNPHistory ADD - CONSTRAINT pk_SNPHistory PRIMARY KEY - ( - snp_id - ) -; - -ALTER TABLE SNPPubmed ADD - CONSTRAINT pk_SNPPubmed PRIMARY KEY - ( - snp_id, - subsnp_id, - pubmed_id, - type - ) -; - - - -ALTER TABLE SNPVal ADD - CONSTRAINT pk_SNPVal PRIMARY KEY - ( - batch_id, - snp_id - ) -; - - -ALTER TABLE SNP_bitfield ADD - CONSTRAINT pk_SNP_bitfield PRIMARY KEY - ( - snp_id - ) -; - -ALTER TABLE SubPop ADD --- CONSTRAINT DF__SubPopB10__last___19AB9A98 DEFAULT (getdate()) FOR last_updated_time, - PRIMARY KEY - ( - batch_id, - subsnp_id, - pop_id, - type - ), ADD - CONSTRAINT fk_SubPop_batch_id FOREIGN KEY - ( - batch_id - ) REFERENCES Batch ( - batch_id - ), ADD - CONSTRAINT fk_SubPop_pop FOREIGN KEY - ( - pop_id - ) REFERENCES Population ( - pop_id - ), ADD - CHECK (type = 'GTY' or (type = 'HET' or type = 'ALE')) -; - -ALTER TABLE SubPopAllele ADD - CONSTRAINT fk_SubPopAllele_bid_ss_pop_type FOREIGN KEY - ( - batch_id, - subsnp_id, - pop_id, - type - ) REFERENCES SubPop ( - batch_id, - subsnp_id, - pop_id, - type - ) ON DELETE CASCADE -; - -ALTER TABLE SubPopGty ADD - CONSTRAINT pk_SubPopGty PRIMARY KEY - ( - subpop_id, - gty_id - ) -; - -ALTER TABLE SubSNP ADD - CONSTRAINT pk_SubSNP PRIMARY KEY - ( - subsnp_id - ) -; - -ALTER TABLE SubSNPAcc_ins ADD - CONSTRAINT pk_SubSNPAcc PRIMARY KEY - ( - subsnp_id, - acc_part, - acc_type_ind - ), ADD - CONSTRAINT ck_SubSNPAcc_ins CHECK (subsnp_id>=(30000001) AND subsnp_id<=(300000000)) -; - -ALTER TABLE SubSNPCommLine_ins ADD - CONSTRAINT pk_SubSNPCommLine PRIMARY KEY - ( - subsnp_id, - line_num - ), ADD - CONSTRAINT ck_SubSNPCommLine_ins CHECK (subsnp_id>=(30000001) AND subsnp_id<=(300000000)) -; - -ALTER TABLE SubSNPHGVS ADD - CONSTRAINT pk_SubSNPHGVS PRIMARY KEY - ( - subsnp_id - ) -; - -ALTER TABLE SubSNPLinkout ADD --- CONSTRAINT DF__SubSNPLin__link___660506BC DEFAULT ('NA') FOR link_type, - CONSTRAINT pk_SubSNPLinkout PRIMARY KEY - ( - subsnp_id, - link_type - ), ADD - CONSTRAINT ck_SubSNPLinkout_link_type CHECK (link_type='NA' OR link_type='OTH' OR link_type='SRC') -; - -ALTER TABLE SubSNPMdFailLn ADD - CONSTRAINT pk_SubSNPMdFailLn PRIMARY KEY - ( - subsnp_id, - line_num - ) -; - -ALTER TABLE SubSNPNoVariSeq ADD - CONSTRAINT pk_SubSNPNoVariSeq PRIMARY KEY - ( - subsnp_id, - line_num - ) -; - -ALTER TABLE SubSNPOmim ADD - CONSTRAINT pk_SubSNPOmim PRIMARY KEY - ( - subsnp_id - ) -; - -ALTER TABLE SubSNPPubmed ADD - CONSTRAINT pk_SubSNPPubmed PRIMARY KEY - ( - subsnp_id, - line_num - ) -; - -ALTER TABLE SubSNPSeq3_ins ADD - CONSTRAINT pk_SubSNPSeq3 PRIMARY KEY - ( - subsnp_id, - type, - line_num - ), ADD - CONSTRAINT ck_SubSNPSeq3_ins CHECK (subsnp_id>=(30000001) AND subsnp_id<=(300000000)) -; - -ALTER TABLE SubSNPSeq5_ins ADD - CONSTRAINT pk_SubSNPSeq5 PRIMARY KEY - ( - subsnp_id, - type, - line_num - ), ADD - CONSTRAINT ck_SubSNPSeq5_ins CHECK (subsnp_id>=(30000001) AND subsnp_id<=(300000000)) -; - -ALTER TABLE SubSNPSeqPos ADD - CONSTRAINT pk_SubSNPSeqPos PRIMARY KEY - ( - subsnp_id -x ) -; - -ALTER TABLE SubSNP_top_or_bot ADD - CONSTRAINT PK__t_ss_top_or_bot__0D5DFFA8 PRIMARY KEY - ( - subsnp_id - ) -; - -ALTER TABLE SubmittedIndividual ADD --- CONSTRAINT DF__Submitted__ploid__41661CE5 DEFAULT (2) FOR ploidy, - CONSTRAINT pk_SubmittedIndividual PRIMARY KEY - ( - pop_id, - loc_ind_id_upp - ) -; - -ALTER TABLE Synonym ADD - CONSTRAINT pk_Synonym PRIMARY KEY - ( - subsnp_id, - type - ) -; - -ALTER TABLE b132_ContigInfo_37_1 ADD - CONSTRAINT pk_b132_ContigInfo PRIMARY KEY - ( - ctg_id - ) -; - - - - - - -ALTER TABLE b132_SNPContigLoc_37_1 ADD - CONSTRAINT ck_snp_type CHECK (snp_type='rs') -; - -ALTER TABLE b132_SNPContigLocusId_37_1 ADD - CONSTRAINT ck_fxn CHECK (fxn_class=(75) OR fxn_class=(73) OR fxn_class=(55) OR fxn_class=(53) OR fxn_class=(45) OR fxn_class=(44) OR fxn_class=(43) OR fxn_class=(42) OR fxn_class=(41) OR fxn_class=(15) OR fxn_class=(13) OR fxn_class=(8) OR fxn_class=(6) OR fxn_class=(3)) -; - -ALTER TABLE b132_SNPMapInfo_37_1 ADD - CONSTRAINT ck_SNPMapInfo_snp_type CHECK (snp_type='rs') -; - -ALTER TABLE dn_IND_batchCount ADD - CONSTRAINT pk_dn_IND_batchCount PRIMARY KEY - ( - batch_id, - pop_id - ) -; - - -ALTER TABLE dn_PopulationIndGrp ADD - CONSTRAINT pk_dn_PopulationIndGrp PRIMARY KEY - ( - pop_id - ) -; - -ALTER TABLE dn_batchCount ADD - CONSTRAINT pk_dn_batchCount PRIMARY KEY - ( - batch_id - ) -; - -ALTER TABLE dn_handleCount ADD - CONSTRAINT pk_dn_handleCount PRIMARY KEY - ( - handle, - batch_type - ) -; - - - +ALTER TABLE AlleleFreqBySsPop ADD CONSTRAINT pk_AlleleFreqBySsPop_b129 PRIMARY KEY (subsnp_id,pop_id,allele_id); +ALTER TABLE Batch ADD CONSTRAINT pk_Batch PRIMARY KEY (batch_id); +ALTER TABLE BatchCita ADD CONSTRAINT pk_BatchCita PRIMARY KEY (batch_id,position); +ALTER TABLE BatchCommLine ADD CONSTRAINT pk_BatchCommLine PRIMARY KEY (batch_id,line_num); +ALTER TABLE BatchCultivar ADD CONSTRAINT pk_BatchCultivar PRIMARY KEY (batch_id,line_num); +ALTER TABLE BatchMeExLine ADD CONSTRAINT pk_BatchMeExLine PRIMARY KEY (batch_id,line_num); +ALTER TABLE BatchStrain ADD CONSTRAINT pk_BatchStrain PRIMARY KEY (batch_id,line_num); +ALTER TABLE BatchValCode ADD CONSTRAINT pk_BatchValCode PRIMARY KEY (batch_id); +ALTER TABLE ClinSigCode ALTER COLUMN create_time SET DEFAULT NOW(); +ALTER TABLE ClinSigCode ALTER COLUMN last_updated_time SET DEFAULT NOW(); +ALTER TABLE ClinSigCode ADD CONSTRAINT PK__ClinSigC__357D4CF85DD3445E PRIMARY KEY (code); +ALTER TABLE Contact ADD CONSTRAINT pk_Contact PRIMARY KEY (batch_id,handle); +ALTER TABLE GeneIdToName ADD CONSTRAINT pk_GeneIdToName PRIMARY KEY (gene_id); +ALTER TABLE GtyFreqBySsPop ADD CONSTRAINT pk_GtyFreqBySsPop_b129 PRIMARY KEY (subsnp_id,pop_id,unigty_id); +ALTER TABLE IndGrpCode ADD CONSTRAINT pk_IndGrpCode PRIMARY KEY (code); +ALTER TABLE IndivBySource ADD CONSTRAINT pk_IndivBySource PRIMARY KEY (src_id,src_ind_id); +ALTER TABLE Individual ADD CONSTRAINT pk_Individual PRIMARY KEY (ind_id); +ALTER TABLE OmimVarLocusIdSNP ADD CONSTRAINT fk_OmimVarLocusIdSNP_snp_id FOREIGN KEY (snp_id) REFERENCES b134_SNP(snp_id); +ALTER TABLE Pedigree ADD CONSTRAINT p_ped_id PRIMARY KEY (ped_id); +ALTER TABLE PedigreeIndividual ADD CONSTRAINT pk_PedigreeIndividual PRIMARY KEY (ped_id,ind_id); +ALTER TABLE PopLine ADD CONSTRAINT pk_PopLine PRIMARY KEY (pop_id,line_num); +ALTER TABLE PopMandLine ADD CONSTRAINT pk_PopMandLine PRIMARY KEY (pop_id,line_num); +ALTER TABLE Population ADD CONSTRAINT pk_Population_pop_id PRIMARY KEY (pop_id); +ALTER TABLE SNPAlleleFreq ADD CONSTRAINT pk_SNPAlleleFreq PRIMARY KEY (snp_id,allele_id); +ALTER TABLE SNPAncestralAllele ALTER COLUMN batch_id SET DEFAULT (0); +ALTER TABLE SNPAncestralAllele ADD CONSTRAINT pk_SNPAncestralAllele PRIMARY KEY (snp_id,batch_id); +ALTER TABLE SNPClinSig ALTER COLUMN upd_time SET DEFAULT NOW(); +ALTER TABLE SNPClinSig ADD CONSTRAINT PK__SNPClinS__430A13A119D3EB23 PRIMARY KEY (snp_id); +ALTER TABLE SNPGtyFreq ADD CONSTRAINT pk_SNPGtyFreq PRIMARY KEY (snp_id,unigty_id); +ALTER TABLE SNPHWProb ADD CONSTRAINT pk_SNPHWProb PRIMARY KEY (snp_id); +ALTER TABLE SNPHistory ADD CONSTRAINT pk_SNPHistory PRIMARY KEY (snp_id); +ALTER TABLE SNPSubSNPLink ADD CONSTRAINT fk_SNPSubSNPLink_ss FOREIGN KEY (subsnp_id) REFERENCES SubSNP(subsnp_id); +ALTER TABLE SNPSuspect ALTER COLUMN upd_time SET DEFAULT NOW(); +ALTER TABLE SNPSuspect ADD CONSTRAINT PK__SNPSuspe__430A13A116D6B790 PRIMARY KEY (snp_id); +ALTER TABLE SNPVal ADD CONSTRAINT pk_SNPVal PRIMARY KEY (batch_id,snp_id); +ALTER TABLE SNP_bitfield ADD CONSTRAINT pk_SNP_bitfield PRIMARY KEY (snp_id); +ALTER TABLE SubPop ALTER COLUMN last_updated_time SET DEFAULT NOW(); +ALTER TABLE SubPop ADD CONSTRAINT fk_SubPop_batch_id FOREIGN KEY (batch_id) REFERENCES Batch(batch_id); +ALTER TABLE SubPop ADD CONSTRAINT fk_SubPop_pop FOREIGN KEY (pop_id) REFERENCES Population(pop_id); +ALTER TABLE SubPop ADD CONSTRAINT PK__SubPop_new__180E3640 PRIMARY KEY (batch_id,subsnp_id,pop_id,type); +ALTER TABLE SubPopAllele ADD CONSTRAINT fk_SubPopAllele_bid_ss_pop_type FOREIGN KEY (batch_id,subsnp_id,pop_id,type) REFERENCES SubPop(batch_id,subsnp_id,pop_id,type); +ALTER TABLE SubPopGty ADD CONSTRAINT pk_SubPopGty PRIMARY KEY (subpop_id,gty_id); +ALTER TABLE SubSNP ADD CONSTRAINT pk_SubSNP PRIMARY KEY (subsnp_id); +ALTER TABLE SubSNPAcc_ins ADD CONSTRAINT pk_SubSNPAcc PRIMARY KEY (subsnp_id,acc_type_ind,acc_part); +ALTER TABLE SubSNPCommLine_ins ADD CONSTRAINT pk_SubSNPCommLine PRIMARY KEY (subsnp_id,line_num); +ALTER TABLE SubSNPHGVS ADD CONSTRAINT pk_SubSNPHGVS PRIMARY KEY (subsnp_id); +ALTER TABLE SubSNPLinkout ALTER COLUMN link_type SET DEFAULT 'NA'; +ALTER TABLE SubSNPLinkout ADD CONSTRAINT pk_SubSNPLinkout PRIMARY KEY (subsnp_id,link_type); +ALTER TABLE SubSNPMdFailLn ADD CONSTRAINT pk_SubSNPMdFailLn PRIMARY KEY (subsnp_id,line_num); +ALTER TABLE SubSNPNoVariSeq ADD CONSTRAINT pk_SubSNPNoVariSeq PRIMARY KEY (subsnp_id,line_num); +ALTER TABLE SubSNPOmim ADD CONSTRAINT pk_SubSNPOmim PRIMARY KEY (subsnp_id); +ALTER TABLE SubSNPPubmed ADD CONSTRAINT pk_SubSNPPubmed PRIMARY KEY (subsnp_id,line_num); +ALTER TABLE SubSNPSeq3_ins ADD CONSTRAINT pk_SubSNPSeq3 PRIMARY KEY (subsnp_id,type,line_num); +ALTER TABLE SubSNPSeq5_ins ADD CONSTRAINT pk_SubSNPSeq5 PRIMARY KEY (subsnp_id,type,line_num); +ALTER TABLE SubSNPSeqPos ADD CONSTRAINT pk_SubSNPSeqPos PRIMARY KEY (subsnp_id); +ALTER TABLE SubSNP_top_or_bot ADD CONSTRAINT PK__t_ss_top_or_bot__0D5DFFA8 PRIMARY KEY (subsnp_id); +ALTER TABLE SubmittedIndividual ALTER COLUMN ploidy SET DEFAULT 2; +ALTER TABLE SubmittedIndividual ADD CONSTRAINT pk_SubmittedIndividual PRIMARY KEY (pop_id,loc_ind_id_upp); +ALTER TABLE Synonym ADD CONSTRAINT pk_Synonym PRIMARY KEY (subsnp_id,type); +ALTER TABLE b135_ContigInfo_37_3 ALTER COLUMN last_updated_time SET DEFAULT NOW(); +ALTER TABLE b135_ContigInfo_37_3 ADD CONSTRAINT pk_b135_ContigInfo_37_3 PRIMARY KEY (contig_gi); +ALTER TABLE b135_MapLink_37_3 ADD CONSTRAINT fk_b135_MapLink_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b135_SNPChrPosOnRef_37_3 ADD CONSTRAINT fk_b135_SNPChrPosOnRef_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b135_SNPContigLoc_37_3 ADD CONSTRAINT fk_b135_SNPContigLoc_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b135_SNPContigLocusId_37_3 ADD CONSTRAINT fk_b135_SNPContigLocusId_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b135_SNPContigProtein_37_3 ADD CONSTRAINT fk_b135_SNPContigProtein_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b135_SNPMapInfo_37_3 ADD CONSTRAINT fk_b135_SNPMapInfo_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE b135_SNPMapLinkProtein_37_3 ADD CONSTRAINT fk_b135_SNPMapLinkProtein_37_3_rs FOREIGN KEY (snp_id) REFERENCES SNP(snp_id); +ALTER TABLE dn_IND_batchCount ADD CONSTRAINT pk_dn_IND_batchCount PRIMARY KEY (batch_id,pop_id); +ALTER TABLE dn_PopulationIndGrp ADD CONSTRAINT pk_dn_PopulationIndGrp PRIMARY KEY (pop_id); +ALTER TABLE dn_batchCount ADD CONSTRAINT pk_dn_batchCount PRIMARY KEY (batch_id); +ALTER TABLE dn_handleCount ADD CONSTRAINT pk_dn_handleCount PRIMARY KEY (handle,batch_type);