· 7 years ago · Nov 22, 2018, 08:24 PM
1
2-- -----------------------------------------------------
3-- Schema mydb
4-- -----------------------------------------------------
5CREATE SCHEMA IF NOT EXISTS "mydb" DEFAULT CHARACTER SET utf8 ;
6USE "mydb" ;
7
8-- -----------------------------------------------------
9-- Table "mydb"."states"
10-- -----------------------------------------------------
11CREATE TABLE IF NOT EXISTS "mydb"."states" (
12 "id" INT NOT NULL AUTO_INCREMENT,
13 "name" VARCHAR(45) NOT NULL,
14 PRIMARY KEY ("id"),
15 UNIQUE INDEX "name_UNIQUE" ("name" ASC) VISIBLE)
16ENGINE = InnoDB;
17
18
19-- -----------------------------------------------------
20-- Table "mydb"."cities"
21-- -----------------------------------------------------
22CREATE TABLE IF NOT EXISTS "mydb"."cities" (
23 "id" INT NOT NULL AUTO_INCREMENT,
24 "name" VARCHAR(45) NOT NULL,
25 PRIMARY KEY ("id"),
26 UNIQUE INDEX "name_UNIQUE" ("name" ASC) VISIBLE)
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table "mydb"."countries"
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS "mydb"."countries" (
34 "id" INT NOT NULL AUTO_INCREMENT,
35 "name" VARCHAR(45) NOT NULL,
36 PRIMARY KEY ("id"),
37 UNIQUE INDEX "name_UNIQUE" ("name" ASC) VISIBLE)
38ENGINE = InnoDB;
39
40
41-- -----------------------------------------------------
42-- Table "mydb"."customers"
43-- -----------------------------------------------------
44CREATE TABLE IF NOT EXISTS "mydb"."customers" (
45 "id" INT NOT NULL AUTO_INCREMENT,
46 "first_name" VARCHAR(30) NOT NULL,
47 "last_name" VARCHAR(30) NOT NULL,
48 "email" VARCHAR(20) NOT NULL,
49 "address" VARCHAR(40) NOT NULL,
50 "postal_code" VARCHAR(10) NULL,
51 "state_id" INT NOT NULL,
52 "city_id" INT NOT NULL,
53 "country_id" INT NOT NULL,
54 PRIMARY KEY ("id"),
55 UNIQUE INDEX "email_UNIQUE" ("email" ASC) VISIBLE,
56 INDEX "fk_customer_state1_idx" ("state_id" ASC) VISIBLE,
57 INDEX "fk_customer_city1_idx" ("city_id" ASC) VISIBLE,
58 INDEX "fk_customer_country1_idx" ("country_id" ASC) VISIBLE,
59 CONSTRAINT "fk_customer_state1"
60 FOREIGN KEY ("state_id")
61 REFERENCES "mydb"."states" ("id")
62 ON DELETE NO ACTION
63 ON UPDATE NO ACTION,
64 CONSTRAINT "fk_customer_city1"
65 FOREIGN KEY ("city_id")
66 REFERENCES "mydb"."cities" ("id")
67 ON DELETE NO ACTION
68 ON UPDATE NO ACTION,
69 CONSTRAINT "fk_customer_country1"
70 FOREIGN KEY ("country_id")
71 REFERENCES "mydb"."countries" ("id")
72 ON DELETE NO ACTION
73 ON UPDATE NO ACTION)
74ENGINE = InnoDB;
75
76
77-- -----------------------------------------------------
78-- Table "mydb"."employees"
79-- -----------------------------------------------------
80CREATE TABLE IF NOT EXISTS "mydb"."employees" (
81 "id" INT NOT NULL AUTO_INCREMENT,
82 "first_name" VARCHAR(25) NOT NULL,
83 "last_name" VARCHAR(25) NOT NULL,
84 "email" VARCHAR(25) NOT NULL,
85 "supervisor_id" INT NOT NULL,
86 PRIMARY KEY ("id"),
87 UNIQUE INDEX "email_UNIQUE" ("email" ASC) VISIBLE,
88 INDEX "fk_employee_employee1_idx" ("supervisor_id" ASC) VISIBLE,
89 CONSTRAINT "fk_employee_employee1"
90 FOREIGN KEY ("supervisor_id")
91 REFERENCES "mydb"."employees" ("id")
92 ON DELETE NO ACTION
93 ON UPDATE NO ACTION)
94ENGINE = InnoDB;
95
96
97-- -----------------------------------------------------
98-- Table "mydb"."products"
99-- -----------------------------------------------------
100CREATE TABLE IF NOT EXISTS "mydb"."products" (
101 "id" INT NOT NULL AUTO_INCREMENT,
102 "name" VARCHAR(45) NOT NULL,
103 "price" VARCHAR(45) NOT NULL,
104 "description" VARCHAR(255) NULL,
105 PRIMARY KEY ("id"),
106 UNIQUE INDEX "name_UNIQUE" ("name" ASC) VISIBLE)
107ENGINE = InnoDB;
108
109
110-- -----------------------------------------------------
111-- Table "mydb"."order_details"
112-- -----------------------------------------------------
113CREATE TABLE IF NOT EXISTS "mydb"."order_details" (
114 "id" INT UNSIGNED NOT NULL,
115 "quantity" INT NOT NULL,
116 "product_id" INT NOT NULL,
117 PRIMARY KEY ("id"),
118 INDEX "fk_order_details_product1_idx" ("product_id" ASC) VISIBLE,
119 CONSTRAINT "fk_order_details_product1"
120 FOREIGN KEY ("product_id")
121 REFERENCES "mydb"."products" ("id")
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION)
124ENGINE = InnoDB;
125
126
127-- -----------------------------------------------------
128-- Table "mydb"."payment_types"
129-- -----------------------------------------------------
130CREATE TABLE IF NOT EXISTS "mydb"."payment_types" (
131 "id" INT NOT NULL AUTO_INCREMENT,
132 "type" VARCHAR(10) NOT NULL,
133 PRIMARY KEY ("id"),
134 UNIQUE INDEX "type_UNIQUE" ("type" ASC) VISIBLE)
135ENGINE = InnoDB;
136
137
138-- -----------------------------------------------------
139-- Table "mydb"."orders"
140-- -----------------------------------------------------
141CREATE TABLE IF NOT EXISTS "mydb"."orders" (
142 "id" INT NOT NULL AUTO_INCREMENT,
143 "order_date" DATE NOT NULL,
144 "ship_date" DATE NULL,
145 "payment_date" DATE NULL,
146 "customer_id" INT NOT NULL,
147 "employee_id" INT NOT NULL,
148 "order_details_id" INT UNSIGNED NOT NULL,
149 "payment_id" INT NOT NULL,
150 PRIMARY KEY ("id"),
151 INDEX "fk_order_customer1_idx" ("customer_id" ASC) VISIBLE,
152 INDEX "fk_order_employee1_idx" ("employee_id" ASC) VISIBLE,
153 INDEX "fk_order_order_details1_idx" ("order_details_id" ASC) VISIBLE,
154 INDEX "fk_order_payment1_idx" ("payment_id" ASC) VISIBLE,
155 CONSTRAINT "fk_order_customer1"
156 FOREIGN KEY ("customer_id")
157 REFERENCES "mydb"."customers" ("id")
158 ON DELETE NO ACTION
159 ON UPDATE NO ACTION,
160 CONSTRAINT "fk_order_employee1"
161 FOREIGN KEY ("employee_id")
162 REFERENCES "mydb"."employees" ("id")
163 ON DELETE NO ACTION
164 ON UPDATE NO ACTION,
165 CONSTRAINT "fk_order_order_details1"
166 FOREIGN KEY ("order_details_id")
167 REFERENCES "mydb"."order_details" ("id")
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION,
170 CONSTRAINT "fk_order_payment1"
171 FOREIGN KEY ("payment_id")
172 REFERENCES "mydb"."payment_types" ("id")
173 ON DELETE NO ACTION
174 ON UPDATE NO ACTION)
175ENGINE = InnoDB;
176
177
178-- -----------------------------------------------------
179-- Table "mydb"."categories"
180-- -----------------------------------------------------
181CREATE TABLE IF NOT EXISTS "mydb"."categories" (
182 "id" INT NOT NULL AUTO_INCREMENT,
183 "name" VARCHAR(45) NOT NULL,
184 "description" VARCHAR(255) NULL,
185 PRIMARY KEY ("id"),
186 UNIQUE INDEX "name_UNIQUE" ("name" ASC) VISIBLE)
187ENGINE = InnoDB;
188
189
190-- -----------------------------------------------------
191-- Table "mydb"."product_categories"
192-- -----------------------------------------------------
193CREATE TABLE IF NOT EXISTS "mydb"."product_categories" (
194 "id" INT NOT NULL,
195 "product_id" INT NOT NULL,
196 "category_id" INT NOT NULL,
197 INDEX "fk_product_category_product1_idx" ("product_id" ASC) VISIBLE,
198 INDEX "fk_product_category_category1_idx" ("category_id" ASC) VISIBLE,
199 PRIMARY KEY ("id"),
200 CONSTRAINT "fk_product_category_product1"
201 FOREIGN KEY ("product_id")
202 REFERENCES "mydb"."products" ("id")
203 ON DELETE NO ACTION
204 ON UPDATE NO ACTION,
205 CONSTRAINT "fk_product_category_category1"
206 FOREIGN KEY ("category_id")
207 REFERENCES "mydb"."categories" ("id")
208 ON DELETE NO ACTION
209 ON UPDATE NO ACTION)
210ENGINE = InnoDB;
211
212
213SET SQL_MODE=@OLD_SQL_MODE;
214SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
215SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;