· 8 years ago · Apr 14, 2018, 04:04 AM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `mydb`;
7
8-- -----------------------------------------------------
9-- Table `mydb`.`pubmed`
10-- -----------------------------------------------------
11DROP TABLE IF EXISTS `mydb`.`pubmed` ;
12
13CREATE TABLE IF NOT EXISTS `mydb`.`pubmed` (
14 `idpubmed` INT NOT NULL ,
15 `PubMed_First` VARCHAR(45) NULL ,
16 `PubMed _other` VARCHAR(45) NULL ,
17 `Author` VARCHAR(45) NULL ,
18 `Year` VARCHAR(45) NULL ,
19 `Title` VARCHAR(45) NULL ,
20 `Summary` VARCHAR(45) NULL ,
21 PRIMARY KEY (`idpubmed`) )
22ENGINE = InnoDB;
23
24
25-- -----------------------------------------------------
26-- Table `mydb`.`Study`
27-- -----------------------------------------------------
28DROP TABLE IF EXISTS `mydb`.`Study` ;
29
30CREATE TABLE IF NOT EXISTS `mydb`.`Study` (
31 `idStudy` INT NOT NULL ,
32 `idpubmed` INT NOT NULL ,
33 `summary` TINYTEXT NULL ,
34 `virus` VARCHAR(45) NULL ,
35 `gene` VARCHAR(45) NULL ,
36 PRIMARY KEY (`idStudy`) ,
37 CONSTRAINT `idpubmed`
38 FOREIGN KEY
39 REFERENCES `mydb`.`pubmed`
40 ON DELETE CASCADE
41 ON UPDATE CASCADE)
42ENGINE = InnoDB;
43
44
45-- -----------------------------------------------------
46-- Table `mydb`.`Patient`
47-- -----------------------------------------------------
48DROP TABLE IF EXISTS `mydb`.`Patient` ;
49
50CREATE TABLE IF NOT EXISTS `mydb`.`Patient` (
51 `idPatient` INT NOT NULL ,
52 `idpubmed` INT NOT NULL ,
53 `PubPatientID` VARCHAR(45) NULL ,
54 `Accession` VARCHAR(45) NULL ,
55 `Race` VARCHAR(45) NULL ,
56 `Gender` VARCHAR(45) NULL ,
57 PRIMARY KEY (`idPatient`) ,
58 INDEX `idpubmed` () ,
59 CONSTRAINT `idpubmed`
60 FOREIGN KEY
61 REFERENCES `mydb`.`pubmed`
62 ON DELETE NO ACTION
63 ON UPDATE NO ACTION)
64ENGINE = InnoDB;
65
66
67-- -----------------------------------------------------
68-- Table `mydb`.`Study_Patient`
69-- -----------------------------------------------------
70DROP TABLE IF EXISTS `mydb`.`Study_Patient` ;
71
72CREATE TABLE IF NOT EXISTS `mydb`.`Study_Patient` (
73 `idStudy_Patient` INT NOT NULL ,
74 `idStudy` INT NOT NULL ,
75 `idPatient` INT NOT NULL ,
76 `File_ID` VARCHAR(45) NULL ,
77 `Dir` VARCHAR(45) NULL ,
78 `Filename` VARCHAR(45) NULL ,
79 `seqLen` INT NULL ,
80 `vStart` INT NULL ,
81 `vEnd` INT NULL ,
82 `gStart` INT NULL ,
83 `gEnd` INT NULL ,
84 PRIMARY KEY (`idStudy_Patient`) ,
85 INDEX `idStudy` () ,
86 INDEX `idPatient` () ,
87 CONSTRAINT `idStudy`
88 FOREIGN KEY ()
89 REFERENCES `mydb`.`Study` ()
90 ON DELETE NO ACTION
91 ON UPDATE NO ACTION,
92 CONSTRAINT `idPatient`
93 FOREIGN KEY
94 REFERENCES `mydb`.`Patient`
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION)
97ENGINE = InnoDB;
98
99
100-- -----------------------------------------------------
101-- Table `mydb`.`Patient_Samples`
102-- -----------------------------------------------------
103DROP TABLE IF EXISTS `mydb`.`Patient_Samples` ;
104
105CREATE TABLE IF NOT EXISTS `mydb`.`Patient_Samples` (
106 `idPatient_Samples` INT NOT NULL ,
107 `idStudy_Patient` INT NOT NULL ,
108 `Time` INT NULL ,
109 `seqCount` INT NULL ,
110 `idTherapy` INT NULL ,
111 `Viral Load copies per ml` INT(11) NULL ,
112 `Viral Load log` INT(11) NULL ,
113 `T Cell Counts` INT(11) NULL ,
114 PRIMARY KEY (`idPatient_Samples`) ,
115 INDEX `idStudy_Patient` () ,
116 CONSTRAINT `idStudy_Patient`
117 FOREIGN KEY
118 REFERENCES `mydb`.`Study_Patient`
119 ON DELETE NO ACTION
120 ON UPDATE NO ACTION)
121ENGINE = InnoDB;
122
123
124
125SET SQL_MODE=@OLD_SQL_MODE;
126SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
127SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
128
129
130
131-------------
132-- Error
133----------------
134near 'REFERENCES `mydb`.`pubmed`
135 ON DELETE CASCADE
136 ON UPDATE CASCADE)
137ENGINE =' at line 10