nNr5HREn

1CREATE DATABASE IF NOT EXISTS `dotnet-shop`;
2USE `dotnet-shop`;
3
4DROP TABLE IF EXISTS `category`;
5CREATE TABLE `category` (
6    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
7    `title` VARCHAR(20) NOT NULL,
8    PRIMARY KEY (`id`),
9    UNIQUE KEY `title_UNIQUE` (`title`)
10)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
11
12DROP TABLE IF EXISTS `customer`;
13CREATE TABLE `customer` (
14    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
15    `first_name` VARCHAR(50) NOT NULL,
16    `last_name` VARCHAR(50) NOT NULL,
17    PRIMARY KEY (`id`)
18)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
19
20DROP TABLE IF EXISTS `vendor`;
21CREATE TABLE `vendor` (
22    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
23    `name` VARCHAR(50) NOT NULL,
24    PRIMARY KEY (`id`),
25    UNIQUE KEY `name_UNIQUE` (`name`)
26)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
27
28DROP TABLE IF EXISTS `product`;
29CREATE TABLE `product` (
30    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
31    `vendor_id` INT(10) UNSIGNED NOT NULL,
32    `title` VARCHAR(50) NOT NULL,
33    `price` DECIMAL(10 , 2 ) UNSIGNED NOT NULL,
34    PRIMARY KEY (`id`),
35    UNIQUE INDEX `vendor_product_title_UNIQUE` (`vendor_id` , `title`),
36    CONSTRAINT `fk_product_vendor_id` FOREIGN KEY (`vendor_id`)
37        REFERENCES `vendor` (`id`)
38        ON DELETE CASCADE ON UPDATE CASCADE
39)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
40
41DROP TABLE IF EXISTS `order`;
42CREATE TABLE `order` (
43    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
44    `customer_id` INT(10) UNSIGNED NOT NULL,
45    PRIMARY KEY (`id`),
46    CONSTRAINT `fk_order_customer_id` FOREIGN KEY (`customer_id`)
47        REFERENCES `customer` (`id`)
48        ON DELETE CASCADE ON UPDATE CASCADE
49)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
50
51DROP TABLE IF EXISTS `category_product`;
52CREATE TABLE `category_product` (
53    `product_id` INT(10) UNSIGNED NOT NULL,
54    `category_id` INT(10) UNSIGNED NOT NULL,
55    PRIMARY KEY (`product_id` , `category_id`),
56    CONSTRAINT `fk_category_product_category_id` FOREIGN KEY (`category_id`)
57        REFERENCES `category` (`id`)
58        ON DELETE CASCADE ON UPDATE CASCADE,
59    CONSTRAINT `fk_category_product_product_id` FOREIGN KEY (`product_id`)
60        REFERENCES `product` (`id`)
61        ON DELETE CASCADE ON UPDATE CASCADE
62)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;
63
64DROP TABLE IF EXISTS `order_product`;
65CREATE TABLE `order_product` (
66    `order_id` INT(10) UNSIGNED NOT NULL,
67    `product_id` INT(10) UNSIGNED NOT NULL,
68    `amount` INT(10) UNSIGNED NOT NULL,
69    PRIMARY KEY (`order_id` , `product_id`),
70    CONSTRAINT `fk_order_product_order_id` FOREIGN KEY (`order_id`)
71        REFERENCES `order` (`id`)
72        ON DELETE CASCADE ON UPDATE CASCADE,
73    CONSTRAINT `fk_order_product_product_id` FOREIGN KEY (`product_id`)
74        REFERENCES `product` (`id`)
75        ON DELETE CASCADE ON UPDATE CASCADE
76)  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4;