· 4 years ago · Jun 01, 2021, 01:00 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Jun 1 15:51:36 2021
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13-- -----------------------------------------------------
14-- Schema seconddb
15-- -----------------------------------------------------
16
17-- -----------------------------------------------------
18-- Schema seconddb
19-- -----------------------------------------------------
20CREATE SCHEMA IF NOT EXISTS `seconddb` DEFAULT CHARACTER SET utf8 ;
21USE `seconddb` ;
22
23-- -----------------------------------------------------
24-- Table `seconddb`.`users`
25-- -----------------------------------------------------
26CREATE TABLE IF NOT EXISTS `seconddb`.`users` (
27 `id` INT NOT NULL AUTO_INCREMENT,
28 `fio` VARCHAR(255) NOT NULL,
29 `login` VARCHAR(255) NULL DEFAULT NULL,
30 `password` VARCHAR(255) NULL DEFAULT NULL,
31 `e_mail` VARCHAR(255) NULL DEFAULT NULL,
32 `type` VARCHAR(45) NULL DEFAULT NULL,
33 PRIMARY KEY (`id`, `fio`),
34 UNIQUE INDEX `login_UNIQUE` (`login` ASC) VISIBLE,
35 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
36ENGINE = InnoDB;
37
38
39-- -----------------------------------------------------
40-- Table `seconddb`.`settings`
41-- -----------------------------------------------------
42CREATE TABLE IF NOT EXISTS `seconddb`.`settings` (
43 `id` INT NOT NULL AUTO_INCREMENT,
44 `host` VARCHAR(45) NULL DEFAULT NULL,
45 `db` VARCHAR(45) NULL DEFAULT NULL,
46 `user` VARCHAR(45) NULL DEFAULT NULL,
47 `password` VARCHAR(45) NULL DEFAULT NULL,
48 PRIMARY KEY (`id`),
49 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
50 CONSTRAINT `user_id`
51 FOREIGN KEY (`id`)
52 REFERENCES `seconddb`.`users` (`id`)
53 ON DELETE NO ACTION
54 ON UPDATE NO ACTION)
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `seconddb`.`Shops`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `seconddb`.`Shops` (
62 `id` INT NOT NULL AUTO_INCREMENT,
63 `name` VARCHAR(255) NULL DEFAULT NULL,
64 `address` VARCHAR(255) NULL DEFAULT NULL,
65 `tel` VARCHAR(100) NULL DEFAULT NULL,
66 `site` VARCHAR(100) NULL DEFAULT NULL,
67 `email` VARCHAR(100) NULL DEFAULT NULL,
68 PRIMARY KEY (`id`),
69 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
70ENGINE = InnoDB;
71
72
73-- -----------------------------------------------------
74-- Table `seconddb`.`product_type`
75-- -----------------------------------------------------
76CREATE TABLE IF NOT EXISTS `seconddb`.`product_type` (
77 `id` INT NOT NULL AUTO_INCREMENT,
78 `name` VARCHAR(255) NULL DEFAULT NULL,
79 PRIMARY KEY (`id`),
80 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
81ENGINE = InnoDB;
82
83
84-- -----------------------------------------------------
85-- Table `seconddb`.`Products`
86-- -----------------------------------------------------
87CREATE TABLE IF NOT EXISTS `seconddb`.`Products` (
88 `id` INT NOT NULL AUTO_INCREMENT,
89 `shop_id` INT NOT NULL,
90 `type_id` INT NOT NULL,
91 `brand` VARCHAR(255) NULL DEFAULT NULL,
92 `model` VARCHAR(255) NULL DEFAULT NULL,
93 `data` TINYTEXT NULL DEFAULT NULL,
94 `img` VARCHAR(255) NULL DEFAULT NULL,
95 `price` VARCHAR(45) NULL DEFAULT NULL,
96 `warranty` VARCHAR(45) NULL DEFAULT NULL,
97 PRIMARY KEY (`id`, `shop_id`, `type_id`),
98 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
99 INDEX `product_to_type_idx` (`type_id` ASC) VISIBLE,
100 INDEX `from_shop_idx` (`shop_id` ASC) VISIBLE,
101 CONSTRAINT `product_to_type`
102 FOREIGN KEY (`type_id`)
103 REFERENCES `seconddb`.`product_type` (`id`)
104 ON DELETE CASCADE
105 ON UPDATE CASCADE,
106 CONSTRAINT `id_from_shop`
107 FOREIGN KEY (`shop_id`)
108 REFERENCES `seconddb`.`Shops` (`id`)
109 ON DELETE CASCADE
110 ON UPDATE CASCADE)
111ENGINE = InnoDB;
112
113
114-- -----------------------------------------------------
115-- Table `seconddb`.`Orders`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `seconddb`.`Orders` (
118 `id` INT NOT NULL AUTO_INCREMENT,
119 `shop_id` INT NOT NULL,
120 `product_id` INT NOT NULL,
121 `fio` VARCHAR(255) NOT NULL,
122 `date` DATE NULL DEFAULT NULL,
123 `quantity` TINYINT NULL DEFAULT NULL,
124 `tel` VARCHAR(100) NULL DEFAULT NULL,
125 `confirm` TINYINT NULL DEFAULT NULL,
126 PRIMARY KEY (`id`, `shop_id`, `product_id`, `fio`),
127 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
128 INDEX `id_from_shop_idx` (`shop_id` ASC) VISIBLE,
129 INDEX `id_from_product_idx` (`product_id` ASC) VISIBLE,
130 INDEX `fio_from_users_idx` (`fio` ASC) VISIBLE,
131 CONSTRAINT `id_from_shop`
132 FOREIGN KEY (`shop_id`)
133 REFERENCES `seconddb`.`Shops` (`id`)
134 ON DELETE CASCADE
135 ON UPDATE CASCADE,
136 CONSTRAINT `id_from_product`
137 FOREIGN KEY (`product_id`)
138 REFERENCES `seconddb`.`Products` (`id`)
139 ON DELETE CASCADE
140 ON UPDATE CASCADE,
141 CONSTRAINT `fio_from_users`
142 FOREIGN KEY (`fio`)
143 REFERENCES `seconddb`.`users` (`fio`)
144 ON DELETE CASCADE
145 ON UPDATE CASCADE)
146ENGINE = InnoDB;
147
148
149-- -----------------------------------------------------
150-- Table `seconddb`.`Deliveries`
151-- -----------------------------------------------------
152CREATE TABLE IF NOT EXISTS `seconddb`.`Deliveries` (
153 `order_id` INT NOT NULL AUTO_INCREMENT,
154 `fio` VARCHAR(255) NOT NULL,
155 `address` VARCHAR(255) NULL DEFAULT NULL,
156 `time` VARCHAR(45) NULL DEFAULT NULL,
157 `date` DATE NULL DEFAULT NULL,
158 `confirm` TINYINT NULL DEFAULT NULL,
159 PRIMARY KEY (`order_id`, `fio`),
160 UNIQUE INDEX `order_id_UNIQUE` (`order_id` ASC) VISIBLE,
161 INDEX `fio_from_users_idx` (`fio` ASC) VISIBLE,
162 CONSTRAINT `id_from_orders`
163 FOREIGN KEY (`order_id`)
164 REFERENCES `seconddb`.`Orders` (`id`)
165 ON DELETE CASCADE
166 ON UPDATE CASCADE,
167 CONSTRAINT `fio_from_users`
168 FOREIGN KEY (`fio`)
169 REFERENCES `seconddb`.`users` (`fio`)
170 ON DELETE CASCADE
171 ON UPDATE CASCADE)
172ENGINE = InnoDB;
173
174
175SET SQL_MODE=@OLD_SQL_MODE;
176SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
177SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
178