X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=schema%2Fshared_schema%2FdbSNP_main_constraint_postgresql.sql;h=66edbcf0386c913ca3d3afe6ab0814610cba7ddc;hb=77ec8a32a887e1eb6364f327f55049cae8e69f95;hp=802b551b14f2cdbfb9fecd98beb7762e226a387f;hpb=6e51d2d339036d36e6294b1bbed27e5c18a6a2f5;p=dbsnp.git

diff --git a/schema/shared_schema/dbSNP_main_constraint_postgresql.sql b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql
index 802b551..66edbcf 100644
--- a/schema/shared_schema/dbSNP_main_constraint_postgresql.sql
+++ b/schema/shared_schema/dbSNP_main_constraint_postgresql.sql
@@ -1,351 +1,67 @@
-ALTER TABLE Allele ADD 
-	CONSTRAINT pk_Allele PRIMARY KEY   
-	(
-		allele_id
-	)
-;
-
-ALTER TABLE AlleleFlagCode ADD 
-	CONSTRAINT pk_AlleleFlagCode PRIMARY KEY   
-	(
-		code
-	)
-;
-
-ALTER TABLE AlleleMotif ADD 
-	CONSTRAINT pk_AlleleMotif PRIMARY KEY   
-	(
-		allele_id,
-		motif_order
-	)
-;
-
-ALTER TABLE AllocIds ADD 
-	CONSTRAINT pk_AllocIds PRIMARY KEY
-	(
-		name
-	)
-;
-
-ALTER TABLE Author ADD 
-	CONSTRAINT pk_Author PRIMARY KEY
-	(
-		pub_id,
-		position
-	)
-;
-
-
-
-ALTER TABLE CpGCode ADD 
-	CONSTRAINT pk_CpGCode PRIMARY KEY   
-	(
-		code
-	)
-;
-
-
-
--- ALTER TABLE GenomeBuildInfo ADD 
--- 	CONSTRAINT DF__GenomeBui__inser__15E52B55 DEFAULT (getdate()) FOR insert_time,
--- 	CONSTRAINT DF__GenomeBui__is_ma__18C19800 DEFAULT (1) FOR is_major,
--- 	CONSTRAINT DF__GenomeBui__ncbi___23FE4082 DEFAULT (0) FOR ncbi_genome_tax_id
--- ;
-
-ALTER TABLE GtyAllele ADD 
-	CONSTRAINT pk_GtyAllele PRIMARY KEY   
-	(
-		gty_id,
-		rev_flag,
-		chr_num
-	)
-;
-
-ALTER TABLE IUPACna ADD 
-	CONSTRAINT pk_IUPACna PRIMARY KEY   
-	(
-		allele
-	)
-;
-
-ALTER TABLE LoadHistory ADD 
-	CONSTRAINT pk_LoadHistory PRIMARY KEY
-	(
-		build_id
-	)
-;
-
-ALTER TABLE LocTypeCode ADD 
---	CONSTRAINT DF__LocTypeCo__creat__75CD617E DEFAULT (getdate()) FOR create_time,
-	CONSTRAINT pk_LocTypeCode PRIMARY KEY   
-	(
-		code
-	)
-;
-
-ALTER TABLE MapLinkCode ADD 
-	CONSTRAINT pk_MapLinkCode PRIMARY KEY   
-	(
-		which_code,
-		code
-	)
-;
-
-ALTER TABLE Method ADD 
-	CONSTRAINT pk_Method_mid PRIMARY KEY   
-	(
-		method_id
-	)
-;
-
-ALTER TABLE MethodClass ADD 
-	CONSTRAINT pk_MethodClass PRIMARY KEY   
-	(
-		meth_class_id
-	)
-;
-
-ALTER TABLE MethodLine ADD 
-	CONSTRAINT pk_MethodLine PRIMARY KEY
-	(
-		method_id,
-		line_num
-	)
-;
-
-ALTER TABLE Moltype ADD 
-	CONSTRAINT pk_Moltype PRIMARY KEY   
-	(
-		code
-	)
-;
-
-ALTER TABLE Motif ADD 
-	CONSTRAINT pk_Motif PRIMARY KEY   
-	(
-		motif_id
-	)
-;
-
-ALTER TABLE ObsGenotype ADD 
-	CONSTRAINT pk_ObsGenotype PRIMARY KEY   
-	(
-		gty_id
-	)
-;
-
-ALTER TABLE ObsVariation ADD 
---	CONSTRAINT DF__ObsVariat__creat__3BB5CE82 DEFAULT (getdate()) FOR create_time,
---	CONSTRAINT DF__ObsVariat__last___3CA9F2BB DEFAULT (getdate()) FOR last_updated_time,
-	CONSTRAINT pk_ObsVariation PRIMARY KEY   
-	(
-		var_id
-	)
-;
-
-ALTER TABLE OrgDbStatus ADD 
-	CONSTRAINT pk_org_db_status PRIMARY KEY   
-	(
-		database_name
-	)
-;
-
-ALTER TABLE OrganismTax ADD 
-	CONSTRAINT pk_organism PRIMARY KEY
-	(
-		tax_id
-	)
-;
-
-ALTER TABLE PopClass ADD 
-	CONSTRAINT pk_PopClass PRIMARY KEY   
-	(
-		pop_id,
-		pop_class_id
-	)
-;
-
-ALTER TABLE PopClassCode ADD 
-	CONSTRAINT PK__PopClassCode__270595B6 PRIMARY KEY   
-	(
-		pop_class_id
-	)
-;
-
-ALTER TABLE Publication ADD 
-	CONSTRAINT pk_Publication PRIMARY KEY
-	(
-		pub_id
-	)
-;
-
-ALTER TABLE SNPGlossary ADD 
---	CONSTRAINT DF__SNPGlossa__last___4050666D DEFAULT (getdate()) FOR last_updated,
-	CONSTRAINT pk_SNPGlossary PRIMARY KEY   
-	(
-		term
-	)
-;
-
-ALTER TABLE SNP_tax_id ADD 
---	CONSTRAINT DF__SNP_tax_i__statu__31583BA0 DEFAULT ('C') FOR status,
-	CONSTRAINT pk_SNP_tax_id PRIMARY KEY   
-	(
-		snp_id
-	)
-;
-
-ALTER TABLE SnpChrCode ADD 
-	CONSTRAINT pk_SnpChrCode PRIMARY KEY   
-	(
-		code
-	)
-;
-
-ALTER TABLE SnpClassCode ADD 
-	CONSTRAINT pk_SnpClassCode PRIMARY KEY   
-	(
-		code
-	)
-;
-
-ALTER TABLE SnpFunctionCode ADD 
-	CONSTRAINT pk_SnpFunctionCode PRIMARY KEY   
-	(
-		code
-	)
-;
-
-ALTER TABLE SnpValidationCode ADD 
-	CONSTRAINT pk_SnpValidationCode PRIMARY KEY
-	(
-		code
-	)
-;
-
-ALTER TABLE StrandCode ADD 
-	CONSTRAINT pk_StrandCode PRIMARY KEY   
-	(
-		code
-	)
-;
-
-
-
-ALTER TABLE Submitter ADD 
---	CONSTRAINT DF_Submitter_create_time DEFAULT (getdate()) FOR create_time,
-	CONSTRAINT pk_Submitter PRIMARY KEY
-	(
-		handle
-	)
-;
-
-ALTER TABLE TemplateType ADD 
-	CONSTRAINT pk_TemplateType PRIMARY KEY   
-	(
-		temp_type_id
-	)
-;
-
-ALTER TABLE UniGty ADD 
-	CONSTRAINT pk_UniGty PRIMARY KEY   
-	(
-		unigty_id
-	)
-;
-
-ALTER TABLE UniVariAllele ADD 
-	CONSTRAINT pk_UniVariAllele PRIMARY KEY   
-	(
-		univar_id,
-		allele_id
-	)
-;
-
-ALTER TABLE UniVariation ADD 
-	CONSTRAINT pk_UniVariation PRIMARY KEY   
-	(
-		univar_id
-	)
-;
-
-ALTER TABLE UniVariationSrcCode ADD 
-	CONSTRAINT pk_UniVariationSrcCode PRIMARY KEY   
-	(
-		code
-	)
-;
-
-ALTER TABLE VariAllele ADD 
-	CONSTRAINT pk_VariAllele PRIMARY KEY   
-	(
-		var_id,
-		allele_id
-	)
-;
-
-ALTER TABLE db_ftp_table_list ADD 
---	CONSTRAINT DF__db_ftp_ta__creat__27F9B9EF DEFAULT (getdate()) FOR create_time,
-	CONSTRAINT pk_db_ftp_table_list PRIMARY KEY   
-	(
-		table_name
-	)
-;
-
--- ALTER TABLE db_map_table_name ADD 
--- 	CONSTRAINT DF__db_map_ta__inNon__206E7217 DEFAULT ('N') FOR inNonHuman
--- ;
-
-ALTER TABLE dn_Allele_rev ADD 
-	CONSTRAINT pk_dn_Allele_rev PRIMARY KEY   
-	(
-		allele_id,
-		rev_flag
-	)
-;
-
-ALTER TABLE dn_Motif_rev ADD 
-	CONSTRAINT pk_dn_Motif_rev PRIMARY KEY   
-	(
-		motif_id,
-		rev_flag
-	)
-;
-
-ALTER TABLE dn_UniGty_allele ADD 
-	CONSTRAINT pk_dn_UniGty_allele PRIMARY KEY   
-	(
-		unigty_id,
-		chr_num
-	)
-;
-
-ALTER TABLE dn_UniGty_rev ADD 
-	CONSTRAINT pk_dn_Gty_rev PRIMARY KEY   
-	(
-		unigty_id,
-		rev_flag
-	)
-;
-
-ALTER TABLE dn_UniVariation_rev ADD 
-	CONSTRAINT pk_dn_UniVariation_rev PRIMARY KEY   
-	(
-		univar_id,
-		rev_flag
-	)
-;
-
-
-
-ALTER TABLE dn_summary ADD 
---	CONSTRAINT DF__dn_summar__creat__383A4359 DEFAULT (getdate()) FOR create_time,
---	CONSTRAINT DF__dn_summar__last___392E6792 DEFAULT (getdate()) FOR last_updated_time,
-	 PRIMARY KEY   
-	(
-		tax_id,
-		build_id,
-		type
-	)
-;
-
+ALTER TABLE Allele ADD CONSTRAINT pk_Allele  PRIMARY KEY   (allele_id ASC);
+ALTER TABLE AlleleFlagCode ADD CONSTRAINT pk_AlleleFlagCode  PRIMARY KEY   (code ASC);
+ALTER TABLE AlleleMotif ADD CONSTRAINT pk_AlleleMotif  PRIMARY KEY   (allele_id ASC,motif_order ASC);
+ALTER TABLE AllocIds ADD CONSTRAINT pk_AllocIds  PRIMARY KEY   (name ASC);
+ALTER TABLE Author ADD CONSTRAINT pk_Author  PRIMARY KEY   (pub_id ASC,position ASC);
+
+
+ALTER TABLE CpGCode ADD CONSTRAINT pk_CpGCode  PRIMARY KEY   (code ASC);
+
+
+ALTER TABLE GenomeBuildInfo ALTER COLUMN insert_time SET DEFAULT NOW();
+ALTER TABLE GenomeBuildInfo ALTER COLUMN is_major SET DEFAULT (1);
+ALTER TABLE GenomeBuildInfo ALTER COLUMN ncbi_genome_tax_id SET DEFAULT (0);
+
+ALTER TABLE GtyAllele ADD CONSTRAINT pk_GtyAllele  PRIMARY KEY   (gty_id ASC,rev_flag ASC,chr_num ASC);
+ALTER TABLE IUPACna ADD CONSTRAINT pk_IUPACna  PRIMARY KEY   (allele ASC);
+ALTER TABLE LoadHistory ADD CONSTRAINT pk_LoadHistory  PRIMARY KEY   (build_id ASC);
+ALTER TABLE LocTypeCode ALTER COLUMN create_time SET DEFAULT NOW();
+ALTER TABLE LocTypeCode ADD CONSTRAINT pk_LocTypeCode  PRIMARY KEY   (code ASC);
+ALTER TABLE MapLinkCode ADD CONSTRAINT pk_MapLinkCode  PRIMARY KEY   (which_code ASC,code ASC);
+ALTER TABLE Method ADD CONSTRAINT pk_Method_mid  PRIMARY KEY   (method_id ASC);
+ALTER TABLE MethodClass ADD CONSTRAINT pk_MethodClass  PRIMARY KEY   (meth_class_id ASC);
+ALTER TABLE MethodLine ADD CONSTRAINT pk_MethodLine  PRIMARY KEY   (method_id ASC,line_num ASC);
+ALTER TABLE Moltype ADD CONSTRAINT pk_Moltype  PRIMARY KEY   (code ASC);
+ALTER TABLE Motif ADD CONSTRAINT pk_Motif  PRIMARY KEY   (motif_id ASC);
+ALTER TABLE ObsGenotype ADD CONSTRAINT pk_ObsGenotype  PRIMARY KEY   (gty_id ASC);
+ALTER TABLE ObsVariation ALTER COLUMN create_time SET DEFAULT NOW();
+ALTER TABLE ObsVariation ALTER COLUMN last_updated_time SET DEFAULT NOW();
+ALTER TABLE ObsVariation ADD CONSTRAINT pk_ObsVariation  PRIMARY KEY   (var_id ASC);
+ALTER TABLE OrgDbStatus ADD CONSTRAINT pk_org_db_status  PRIMARY KEY   (database_name ASC);
+ALTER TABLE OrganismTax ADD CONSTRAINT pk_organism  PRIMARY KEY   (tax_id ASC);
+ALTER TABLE PopClass ADD CONSTRAINT pk_PopClass  PRIMARY KEY   (pop_id ASC,pop_class_id ASC);
+ALTER TABLE PopClassCode ADD CONSTRAINT PK__PopClassCode__270595B6  PRIMARY KEY   (pop_class_id ASC);
+ALTER TABLE Publication ADD CONSTRAINT pk_Publication  PRIMARY KEY   (pub_id ASC);
+ALTER TABLE SNPGlossary ALTER COLUMN last_updated SET DEFAULT NOW();
+ALTER TABLE SNPGlossary ADD CONSTRAINT pk_SNPGlossary  PRIMARY KEY   (term ASC);
+ALTER TABLE SNP_tax_id ALTER COLUMN status SET DEFAULT 'C';
+ALTER TABLE SNP_tax_id ADD CONSTRAINT pk_SNP_tax_id  PRIMARY KEY   (snp_id ASC);
+ALTER TABLE SnpChrCode ADD CONSTRAINT pk_SnpChrCode  PRIMARY KEY   (code ASC);
+ALTER TABLE SnpClassCode ADD CONSTRAINT pk_SnpClassCode  PRIMARY KEY   (code ASC);
+ALTER TABLE SnpFunctionCode ADD CONSTRAINT pk_SnpFunctionCode  PRIMARY KEY   (code ASC);
+ALTER TABLE SnpValidationCode ADD CONSTRAINT pk_SnpValidationCode  PRIMARY KEY   (code ASC);
+ALTER TABLE StrandCode ADD CONSTRAINT pk_StrandCode  PRIMARY KEY   (code ASC);
+
+
+ALTER TABLE Submitter ALTER COLUMN create_time SET DEFAULT NOW();
+ALTER TABLE Submitter ADD CONSTRAINT pk_Submitter  PRIMARY KEY   (handle ASC);
+ALTER TABLE TemplateType ADD CONSTRAINT pk_TemplateType  PRIMARY KEY   (temp_type_id ASC);
+ALTER TABLE UniGty ADD CONSTRAINT pk_UniGty  PRIMARY KEY   (unigty_id ASC);
+ALTER TABLE UniVariAllele ADD CONSTRAINT pk_UniVariAllele  PRIMARY KEY   (univar_id ASC,allele_id ASC);
+ALTER TABLE UniVariation ADD CONSTRAINT pk_UniVariation  PRIMARY KEY   (univar_id ASC);
+ALTER TABLE UniVariationSrcCode ADD CONSTRAINT pk_UniVariationSrcCode  PRIMARY KEY   (code ASC);
+ALTER TABLE VariAllele ADD CONSTRAINT pk_VariAllele  PRIMARY KEY   (var_id ASC,allele_id ASC);
+ALTER TABLE db_ftp_table_list ALTER COLUMN create_time SET DEFAULT NOW();
+ALTER TABLE db_ftp_table_list ADD CONSTRAINT pk_db_ftp_table_list  PRIMARY KEY   (table_name ASC);
+ALTER TABLE db_map_table_name ALTER COLUMN inNonHuman SET DEFAULT 'N';
+ALTER TABLE db_map_table_name ADD CONSTRAINT pk_db_map_table_name  PRIMARY KEY   (table_name ASC);
+ALTER TABLE dn_Allele_rev ADD CONSTRAINT pk_dn_Allele_rev  PRIMARY KEY   (allele_id ASC,rev_flag ASC);
+ALTER TABLE dn_Motif_rev ADD CONSTRAINT pk_dn_Motif_rev  PRIMARY KEY   (motif_id ASC,rev_flag ASC);
+ALTER TABLE dn_UniGty_allele ADD CONSTRAINT pk_dn_UniGty_allele  PRIMARY KEY   (unigty_id ASC,chr_num ASC);
+ALTER TABLE dn_UniGty_rev ADD CONSTRAINT pk_dn_Gty_rev  PRIMARY KEY   (unigty_id ASC,rev_flag ASC);
+ALTER TABLE dn_UniVariation_rev ADD CONSTRAINT pk_dn_UniVariation_rev  PRIMARY KEY   (univar_id ASC,rev_flag ASC);
+
+
+ALTER TABLE dn_summary ALTER COLUMN create_time SET DEFAULT NOW();
+ALTER TABLE dn_summary ALTER COLUMN last_updated_time SET DEFAULT NOW();
+ALTER TABLE dn_summary ADD CONSTRAINT PK__dn_summary__3651FAE7  PRIMARY KEY   (tax_id ASC,build_id ASC,type ASC);