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