schema/human_9606_schema/human_9606_constraint.sql

   1 ALTER TABLE [AlleleFreqBySsPop] ADD
   2         CONSTRAINT [pk_AlleleFreqBySsPop_b129] PRIMARY KEY  CLUSTERED
   3         (
   4                 [subsnp_id],
   5                 [pop_id],
   6                 [allele_id]
   7         )
   8 GO
   9
  10
  11 ALTER TABLE [Batch] ADD
  12         CONSTRAINT [pk_Batch] PRIMARY KEY  CLUSTERED
  13         (
  14                 [batch_id]
  15         ),
  16         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'))))))
  17 GO
  18
  19
  20 ALTER TABLE [BatchCita] ADD
  21         CONSTRAINT [pk_BatchCita] PRIMARY KEY  NONCLUSTERED
  22         (
  23                 [batch_id],
  24                 [position]
  25         )
  26 GO
  27
  28
  29 ALTER TABLE [BatchCommLine] ADD
  30         CONSTRAINT [pk_BatchCommLine] PRIMARY KEY  NONCLUSTERED
  31         (
  32                 [batch_id],
  33                 [line_num]
  34         )
  35 GO
  36
  37
  38 ALTER TABLE [BatchCultivar] ADD
  39         CONSTRAINT [pk_BatchCultivar] PRIMARY KEY  NONCLUSTERED
  40         (
  41                 [batch_id],
  42                 [line_num]
  43         )
  44 GO
  45
  46
  47 ALTER TABLE [BatchMeExLine] ADD
  48         CONSTRAINT [pk_BatchMeExLine] PRIMARY KEY  NONCLUSTERED
  49         (
  50                 [batch_id],
  51                 [line_num]
  52         )
  53 GO
  54
  55
  56 ALTER TABLE [BatchStrain] ADD
  57         CONSTRAINT [pk_BatchStrain] PRIMARY KEY  NONCLUSTERED
  58         (
  59                 [batch_id],
  60                 [line_num]
  61         )
  62 GO
  63
  64
  65 ALTER TABLE [BatchValCode] ADD
  66         CONSTRAINT [pk_BatchValCode] PRIMARY KEY  CLUSTERED
  67         (
  68                 [batch_id]
  69         )
  70 GO
  71
  72
  73 ALTER TABLE [Contact] ADD
  74         CONSTRAINT [pk_Contact] PRIMARY KEY  NONCLUSTERED
  75         (
  76                 [batch_id],
  77                 [handle]
  78         )
  79 GO
  80
  81
  82 ALTER TABLE [FreqSummaryBySsPop] ADD
  83         CONSTRAINT [pk_FreqSummaryBySsPop] PRIMARY KEY  CLUSTERED
  84         (
  85                 [subsnp_id],
  86                 [pop_id]
  87         )
  88 GO
  89
  90
  91 ALTER TABLE [GeneIdToName] ADD
  92         CONSTRAINT [pk_GeneIdToName] PRIMARY KEY  CLUSTERED
  93         (
  94                 [gene_id]
  95         )
  96 GO
  97
  98
  99 ALTER TABLE [GtyFreqBySsPop] ADD
 100         CONSTRAINT [pk_GtyFreqBySsPop_b129] PRIMARY KEY  CLUSTERED
 101         (
 102                 [subsnp_id],
 103                 [pop_id],
 104                 [unigty_id]
 105         )
 106 GO
 107
 108
 109 ALTER TABLE [IndGrpCode] ADD
 110         CONSTRAINT [pk_IndGrpCode] PRIMARY KEY  CLUSTERED
 111         (
 112                 [code]
 113         )
 114 GO
 115
 116
 117 ALTER TABLE [IndivBySource] ADD
 118         CONSTRAINT [pk_IndivBySource] PRIMARY KEY  NONCLUSTERED
 119         (
 120                 [src_id],
 121                 [src_ind_id]
 122         )
 123 GO
 124
 125
 126
 127 ALTER TABLE [Individual] ADD
 128         CONSTRAINT [pk_Individual] PRIMARY KEY  CLUSTERED
 129         (
 130                 [ind_id]
 131         )
 132 GO
 133
 134
 135
 136 ALTER TABLE [Pedigree] ADD
 137         CONSTRAINT [p_ped_id] PRIMARY KEY  CLUSTERED
 138         (
 139                 [ped_id]
 140         )
 141 GO
 142
 143
 144 ALTER TABLE [PedigreeIndividual] ADD
 145         CONSTRAINT [pk_PedigreeIndividual] PRIMARY KEY  CLUSTERED
 146         (
 147                 [ped_id],
 148                 [ind_id]
 149         )
 150 GO
 151
 152
 153 ALTER TABLE [PopLine] ADD
 154         CONSTRAINT [pk_PopLine] PRIMARY KEY  CLUSTERED
 155         (
 156                 [pop_id],
 157                 [line_num]
 158         )
 159 GO
 160
 161
 162 ALTER TABLE [PopMandLine] ADD
 163         CONSTRAINT [pk_PopMandLine] PRIMARY KEY  NONCLUSTERED
 164         (
 165                 [pop_id],
 166                 [line_num]
 167         )
 168 GO
 169
 170
 171 ALTER TABLE [Population] ADD
 172         CONSTRAINT [pk_Population_pop_id] PRIMARY KEY  CLUSTERED
 173         (
 174                 [pop_id]
 175         )
 176 GO
 177
 178
 179
 180
 181
 182 ALTER TABLE [SNPAlleleFreq] ADD
 183         CONSTRAINT [pk_SNPAlleleFreq] PRIMARY KEY  CLUSTERED
 184         (
 185                 [snp_id],
 186                 [allele_id]
 187         )
 188 GO
 189
 190
 191 ALTER TABLE [SNPAncestralAllele] ADD
 192         CONSTRAINT [DF__SNPAncest__batch__664F5149] DEFAULT ((0)) FOR [batch_id],
 193         CONSTRAINT [pk_SNPAncestralAllele] PRIMARY KEY  CLUSTERED
 194         (
 195                 [snp_id],
 196                 [batch_id]
 197         )
 198 GO
 199
 200
 201 ALTER TABLE [SNPGtyFreq] ADD
 202         CONSTRAINT [pk_SNPGtyFreq] PRIMARY KEY  CLUSTERED
 203         (
 204                 [snp_id],
 205                 [unigty_id]
 206         )
 207 GO
 208
 209
 210 ALTER TABLE [SNPHWProb] ADD
 211         CONSTRAINT [pk_SNPHWProb] PRIMARY KEY  CLUSTERED
 212         (
 213                 [snp_id]
 214         )
 215 GO
 216
 217
 218 ALTER TABLE [SNPHistory] ADD
 219         CONSTRAINT [pk_SNPHistory] PRIMARY KEY  CLUSTERED
 220         (
 221                 [snp_id]
 222         )
 223 GO
 224
 225
 226 ALTER TABLE [SNPPubmed] ADD
 227         CONSTRAINT [pk_SNPPubmed] PRIMARY KEY  CLUSTERED
 228         (
 229                 [snp_id],
 230                 [subsnp_id],
 231                 [pubmed_id],
 232                 [type]
 233         )
 234 GO
 235
 236
 237
 238
 239 ALTER TABLE [SNPVal] ADD
 240         CONSTRAINT [pk_SNPVal] PRIMARY KEY  CLUSTERED
 241         (
 242                 [batch_id],
 243                 [snp_id]
 244         )
 245 GO
 246
 247
 248
 249 ALTER TABLE [SNP_bitfield] ADD
 250         CONSTRAINT [pk_SNP_bitfield] PRIMARY KEY  CLUSTERED
 251         (
 252                 [snp_id]
 253         )
 254 GO
 255
 256
 257 ALTER TABLE [SubPop] ADD
 258         CONSTRAINT [DF__SubPopB10__last___19AB9A98] DEFAULT (getdate()) FOR [last_updated_time],
 259          PRIMARY KEY  CLUSTERED
 260         (
 261                 [batch_id],
 262                 [subsnp_id],
 263                 [pop_id],
 264                 [type]
 265         ),
 266         CONSTRAINT [fk_SubPop_batch_id] FOREIGN KEY
 267         (
 268                 [batch_id]
 269         ) REFERENCES [Batch] (
 270                 [batch_id]
 271         ),
 272         CONSTRAINT [fk_SubPop_pop] FOREIGN KEY
 273         (
 274                 [pop_id]
 275         ) REFERENCES [Population] (
 276                 [pop_id]
 277         ),
 278          CHECK ([type] = 'GTY' or ([type] = 'HET' or [type] = 'ALE'))
 279 GO
 280
 281
 282 ALTER TABLE [SubPopAllele] ADD
 283         CONSTRAINT [fk_SubPopAllele_bid_ss_pop_type] FOREIGN KEY
 284         (
 285                 [batch_id],
 286                 [subsnp_id],
 287                 [pop_id],
 288                 [type]
 289         ) REFERENCES [SubPop] (
 290                 [batch_id],
 291                 [subsnp_id],
 292                 [pop_id],
 293                 [type]
 294         ) ON DELETE CASCADE
 295 GO
 296
 297
 298 ALTER TABLE [SubPopGty] ADD
 299         CONSTRAINT [pk_SubPopGty] PRIMARY KEY  CLUSTERED
 300         (
 301                 [subpop_id],
 302                 [gty_id]
 303         )
 304 GO
 305
 306
 307 ALTER TABLE [SubSNP] ADD
 308         CONSTRAINT [pk_SubSNP] PRIMARY KEY  CLUSTERED
 309         (
 310                 [subsnp_id]
 311         )
 312 GO
 313
 314
 315 ALTER TABLE [SubSNPAcc_ins] ADD
 316         CONSTRAINT [pk_SubSNPAcc] PRIMARY KEY  CLUSTERED
 317         (
 318                 [subsnp_id],
 319                 [acc_part],
 320                 [acc_type_ind]
 321         ),
 322         CONSTRAINT [ck_SubSNPAcc_ins] CHECK ([subsnp_id]>=(30000001) AND [subsnp_id]<=(300000000))
 323 GO
 324
 325
 326 ALTER TABLE [SubSNPCommLine_ins] ADD
 327         CONSTRAINT [pk_SubSNPCommLine] PRIMARY KEY  CLUSTERED
 328         (
 329                 [subsnp_id],
 330                 [line_num]
 331         ),
 332         CONSTRAINT [ck_SubSNPCommLine_ins] CHECK ([subsnp_id]>=(30000001) AND [subsnp_id]<=(300000000))
 333 GO
 334
 335
 336 ALTER TABLE [SubSNPHGVS] ADD
 337         CONSTRAINT [pk_SubSNPHGVS] PRIMARY KEY  CLUSTERED
 338         (
 339                 [subsnp_id]
 340         )
 341 GO
 342
 343
 344 ALTER TABLE [SubSNPLinkout] ADD
 345         CONSTRAINT [DF__SubSNPLin__link___660506BC] DEFAULT ('NA') FOR [link_type],
 346         CONSTRAINT [pk_SubSNPLinkout] PRIMARY KEY  CLUSTERED
 347         (
 348                 [subsnp_id],
 349                 [link_type]
 350         ),
 351         CONSTRAINT [ck_SubSNPLinkout_link_type] CHECK ([link_type]='NA' OR [link_type]='OTH' OR [link_type]='SRC')
 352 GO
 353
 354
 355 ALTER TABLE [SubSNPMdFailLn] ADD
 356         CONSTRAINT [pk_SubSNPMdFailLn] PRIMARY KEY  NONCLUSTERED
 357         (
 358                 [subsnp_id],
 359                 [line_num]
 360         )
 361 GO
 362
 363
 364 ALTER TABLE [SubSNPNoVariSeq] ADD
 365         CONSTRAINT [pk_SubSNPNoVariSeq] PRIMARY KEY  NONCLUSTERED
 366         (
 367                 [subsnp_id],
 368                 [line_num]
 369         )
 370 GO
 371
 372
 373 ALTER TABLE [SubSNPOmim] ADD
 374         CONSTRAINT [pk_SubSNPOmim] PRIMARY KEY  CLUSTERED
 375         (
 376                 [subsnp_id]
 377         )
 378 GO
 379
 380
 381 ALTER TABLE [SubSNPPubmed] ADD
 382         CONSTRAINT [pk_SubSNPPubmed] PRIMARY KEY  CLUSTERED
 383         (
 384                 [subsnp_id],
 385                 [line_num]
 386         )
 387 GO
 388
 389
 390 ALTER TABLE [SubSNPSeq3_ins] ADD
 391         CONSTRAINT [pk_SubSNPSeq3] PRIMARY KEY  CLUSTERED
 392         (
 393                 [subsnp_id],
 394                 [type],
 395                 [line_num]
 396         ),
 397         CONSTRAINT [ck_SubSNPSeq3_ins] CHECK ([subsnp_id]>=(30000001) AND [subsnp_id]<=(300000000))
 398 GO
 399
 400
 401 ALTER TABLE [SubSNPSeq5_ins] ADD
 402         CONSTRAINT [pk_SubSNPSeq5] PRIMARY KEY  CLUSTERED
 403         (
 404                 [subsnp_id],
 405                 [type],
 406                 [line_num]
 407         ),
 408         CONSTRAINT [ck_SubSNPSeq5_ins] CHECK ([subsnp_id]>=(30000001) AND [subsnp_id]<=(300000000))
 409 GO
 410
 411
 412 ALTER TABLE [SubSNPSeqPos] ADD
 413         CONSTRAINT [pk_SubSNPSeqPos] PRIMARY KEY  CLUSTERED
 414         (
 415                 [subsnp_id]
 416         )
 417 GO
 418
 419
 420 ALTER TABLE [SubSNP_top_or_bot] ADD
 421         CONSTRAINT [PK__t_ss_top_or_bot__0D5DFFA8] PRIMARY KEY  CLUSTERED
 422         (
 423                 [subsnp_id]
 424         )
 425 GO
 426
 427
 428 ALTER TABLE [SubmittedIndividual] ADD
 429         CONSTRAINT [DF__Submitted__ploid__41661CE5] DEFAULT (2) FOR [ploidy],
 430         CONSTRAINT [pk_SubmittedIndividual] PRIMARY KEY  CLUSTERED
 431         (
 432                 [pop_id],
 433                 [loc_ind_id_upp]
 434         )
 435 GO
 436
 437
 438 ALTER TABLE [Synonym] ADD
 439         CONSTRAINT [pk_Synonym] PRIMARY KEY  NONCLUSTERED
 440         (
 441                 [subsnp_id],
 442                 [type]
 443         )
 444 GO
 445
 446
 447 ALTER TABLE [b132_ContigInfo_37_1] ADD
 448         CONSTRAINT [pk_b132_ContigInfo] PRIMARY KEY  CLUSTERED
 449         (
 450                 [ctg_id]
 451         )
 452 GO
 453
 454
 455
 456
 457
 458
 459
 460 ALTER TABLE [b132_SNPContigLoc_37_1] ADD
 461         CONSTRAINT [ck_snp_type] CHECK ([snp_type]='rs')
 462 GO
 463
 464
 465 ALTER TABLE [b132_SNPContigLocusId_37_1] ADD
 466         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))
 467 GO
 468
 469
 470 ALTER TABLE [b132_SNPMapInfo_37_1] ADD
 471         CONSTRAINT [ck_SNPMapInfo_snp_type] CHECK ([snp_type]='rs')
 472 GO
 473
 474
 475 ALTER TABLE [dn_IND_batchCount] ADD
 476         CONSTRAINT [pk_dn_IND_batchCount] PRIMARY KEY  NONCLUSTERED
 477         (
 478                 [batch_id],
 479                 [pop_id]
 480         )
 481 GO
 482
 483
 484
 485 ALTER TABLE [dn_PopulationIndGrp] ADD
 486         CONSTRAINT [pk_dn_PopulationIndGrp] PRIMARY KEY  CLUSTERED
 487         (
 488                 [pop_id]
 489         )
 490 GO
 491
 492
 493 ALTER TABLE [dn_batchCount] ADD
 494         CONSTRAINT [pk_dn_batchCount] PRIMARY KEY  NONCLUSTERED
 495         (
 496                 [batch_id]
 497         )
 498 GO
 499
 500
 501 ALTER TABLE [dn_handleCount] ADD
 502         CONSTRAINT [pk_dn_handleCount] PRIMARY KEY  NONCLUSTERED
 503         (
 504                 [handle],
 505                 [batch_type]
 506         )
 507 GO
 508
 509
 510
 511