add b137 schema changes for the human tables

[dbsnp.git] / schema / human_9606_schema / human_9606_constraint.sql

diff --git a/schema/human_9606_schema/human_9606_constraint.sql b/schema/human_9606_schema/human_9606_constraint.sql
index 396b03c95735af1688a71e6e55fb109027efde01..4e56b363f92e11c2bad11de1fa6af625bf37a9ea 100644 (file)
--- a/schema/human_9606_schema/human_9606_constraint.sql
+++ b/schema/human_9606_schema/human_9606_constraint.sql
@@ -24,6 +24,7 @@ GO
 
 ALTER TABLE [ClinSigCode] ADD CONSTRAINT [DF__ClinSigCo__creat__5FBB8CD0] DEFAULT (GETDATE()) FOR [create_time]
 ALTER TABLE [ClinSigCode] ADD CONSTRAINT [DF__ClinSigCo__last___60AFB109] DEFAULT (GETDATE()) FOR [last_updated_time]
+ALTER TABLE [ClinSigCode] ADD CONSTRAINT [DF__ClinSigCo__sever__58B68DF4] DEFAULT ((0)) FOR [severity_level]
 ALTER TABLE [ClinSigCode] ADD CONSTRAINT [PK__ClinSigC__357D4CF85DD3445E]  PRIMARY KEY  CLUSTERED ([code] ASC)\r
 GO
 
@@ -47,7 +48,7 @@ GO
 ALTER TABLE [Individual] ADD CONSTRAINT [pk_Individual]  PRIMARY KEY  CLUSTERED ([ind_id] ASC)\r
 GO
 
-ALTER TABLE [OmimVarLocusIdSNP] ADD CONSTRAINT [fk_OmimVarLocusIdSNP_snp_id] FOREIGN KEY (snp_id) REFERENCES [b134_SNP](snp_id)\r
+ALTER TABLE [OmimVarLocusIdSNP] ADD CONSTRAINT [fk_OmimVarLocusIdSNP_snp_id] FOREIGN KEY (snp_id) REFERENCES [b135_SNP](snp_id)\r
 GO
 
 ALTER TABLE [Pedigree] ADD CONSTRAINT [p_ped_id]  PRIMARY KEY  CLUSTERED ([ped_id] ASC)\r
@@ -76,9 +77,6 @@ ALTER TABLE [SNPAncestralAllele] ADD CONSTRAINT [DF__SNPAncest__batch__664F5149]
 ALTER TABLE [SNPAncestralAllele] ADD CONSTRAINT [pk_SNPAncestralAllele]  PRIMARY KEY  CLUSTERED ([snp_id] ASC,[batch_id] ASC)\r
 GO
 
-ALTER TABLE [SNPClinSig] ADD CONSTRAINT [DF__SNPClinSi__upd_t__1BBC3395] DEFAULT (GETDATE()) FOR [upd_time]
-ALTER TABLE [SNPClinSig] ADD CONSTRAINT [PK__SNPClinS__430A13A119D3EB23]  PRIMARY KEY  CLUSTERED ([snp_id] ASC)\r
-GO
 
 ALTER TABLE [SNPGtyFreq] ADD CONSTRAINT [pk_SNPGtyFreq]  PRIMARY KEY  CLUSTERED ([snp_id] ASC,[unigty_id] ASC)\r
 GO
@@ -90,8 +88,6 @@ ALTER TABLE [SNPHistory] ADD CONSTRAINT [pk_SNPHistory]  PRIMARY KEY  CLUSTERED
 GO
 
 
-ALTER TABLE [SNPSubSNPLink] ADD CONSTRAINT [fk_SNPSubSNPLink_ss] FOREIGN KEY (subsnp_id) REFERENCES [SubSNP](subsnp_id)\r
-GO
 
 
 ALTER TABLE [SNPSuspect] ADD CONSTRAINT [DF__SNPSuspec__upd_t__18BF0002] DEFAULT (GETDATE()) FOR [upd_time]
@@ -101,6 +97,8 @@ GO
 ALTER TABLE [SNPVal] ADD CONSTRAINT [pk_SNPVal]  PRIMARY KEY  CLUSTERED ([batch_id] ASC,[snp_id] ASC)\r
 GO
 
+ALTER TABLE [SNP_HGVS] ADD CONSTRAINT [fk_SNP_HGVS] FOREIGN KEY (snp_id) REFERENCES [b135_SNP](snp_id)\r
+GO
 
 ALTER TABLE [SNP_bitfield] ADD CONSTRAINT [pk_SNP_bitfield]  PRIMARY KEY  CLUSTERED ([snp_id] ASC)\r
 GO
@@ -163,38 +161,39 @@ ALTER TABLE [SubmittedIndividual] ADD CONSTRAINT [DF__Submitted__ploid__41661CE5
 ALTER TABLE [SubmittedIndividual] ADD CONSTRAINT [pk_SubmittedIndividual]  PRIMARY KEY  CLUSTERED ([pop_id] ASC,[loc_ind_id_upp] ASC)\r
 GO
 
+ALTER TABLE [SuspectReasonCode] ADD CONSTRAINT [DF__SuspectRe__creat__130626AC] DEFAULT (GETDATE()) FOR [create_time]
+ALTER TABLE [SuspectReasonCode] ADD CONSTRAINT [DF__SuspectRe__last___13FA4AE5] DEFAULT (GETDATE()) FOR [last_update_time]
+ALTER TABLE [SuspectReasonCode] ADD CONSTRAINT [PK__SuspectR__357D4CF8111DDE3A]  PRIMARY KEY  CLUSTERED ([code] ASC)\r
+GO
+
 ALTER TABLE [Synonym] ADD CONSTRAINT [pk_Synonym]  PRIMARY KEY  NONCLUSTERED ([subsnp_id] ASC,[type] ASC)\r
 GO
 
-ALTER TABLE [b135_ContigInfo_37_3] ADD CONSTRAINT [df_b135_ContigInfo_37_3_last_updated_time] DEFAULT (GETDATE()) FOR [last_updated_time]
-ALTER TABLE [b135_ContigInfo_37_3] ADD CONSTRAINT [pk_b135_ContigInfo_37_3]  PRIMARY KEY  CLUSTERED ([contig_gi] ASC)\r
+ALTER TABLE [b137_ContigInfo] ADD CONSTRAINT [df_b137_ContigInfo_last_updated_time] DEFAULT (GETDATE()) FOR [last_updated_time]
+ALTER TABLE [b137_ContigInfo] ADD CONSTRAINT [pk_b137_ContigInfo]  PRIMARY KEY  CLUSTERED ([contig_gi] ASC)\r
 GO
 
+ALTER TABLE [b137_MapLink] ADD CONSTRAINT [fk_b137_MapLink_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
+GO
 
 
-ALTER TABLE [b135_MapLink_37_3] ADD CONSTRAINT [fk_b135_MapLink_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
-GO
 
 
 
-ALTER TABLE [b135_SNPChrPosOnRef_37_3] ADD CONSTRAINT [fk_b135_SNPChrPosOnRef_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
+ALTER TABLE [b137_SNPChrPosOnRef] ADD CONSTRAINT [fk_b137_SNPChrPosOnRef_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
 GO
 
-ALTER TABLE [b135_SNPContigLoc_37_3] ADD CONSTRAINT [fk_b135_SNPContigLoc_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
+ALTER TABLE [b137_SNPContigLoc] ADD CONSTRAINT [fk_b137_SNPContigLoc_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
 GO
-
-ALTER TABLE [b135_SNPContigLocusId_37_3] ADD CONSTRAINT [fk_b135_SNPContigLocusId_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
+ALTER TABLE [b137_SNPContigLoc] ADD CONSTRAINT [pk_b137_SNPContigLoc]  PRIMARY KEY  CLUSTERED ([snp_type] ASC,[snp_id] ASC,[ctg_id] ASC,[asn_from] ASC)\r
 GO
 
-ALTER TABLE [b135_SNPContigProtein_37_3] ADD CONSTRAINT [fk_b135_SNPContigProtein_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
-GO
 
-ALTER TABLE [b135_SNPMapInfo_37_3] ADD CONSTRAINT [fk_b135_SNPMapInfo_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
-GO
 
-ALTER TABLE [b135_SNPMapLinkProtein_37_3] ADD CONSTRAINT [fk_b135_SNPMapLinkProtein_37_3_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
+ALTER TABLE [b137_SNPMapInfo] ADD CONSTRAINT [fk_b137_SNPMapInfo_rs] FOREIGN KEY (snp_id) REFERENCES [SNP](snp_id)\r
 GO
 
+
 ALTER TABLE [dn_IND_batchCount] ADD CONSTRAINT [pk_dn_IND_batchCount]  PRIMARY KEY  NONCLUSTERED ([batch_id] ASC,[pop_id] ASC)\r
 GO