· 6 years ago · Nov 28, 2019, 02:38 PM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Nov 28 14:33:36 2019
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-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`postal_code`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`postal_code` (
24 `id` INT(11) NOT NULL,
25 `pc4` INT(11) NOT NULL,
26 `pc3` INT(11) NOT NULL,
27 `pcalpha` VARCHAR(25) NOT NULL,
28 PRIMARY KEY (`id`))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `mydb`.`customer`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
36 `id` INT(11) NOT NULL,
37 `description` VARCHAR(100) NOT NULL,
38 `address` VARCHAR(45) NULL,
39 `postal_code` INT(11) NULL,
40 PRIMARY KEY (`id`),
41 UNIQUE INDEX `description_UNIQUE` (`description` ASC) VISIBLE,
42 INDEX `fk_customer_postal_code_idx` (`postal_code` ASC) VISIBLE,
43 CONSTRAINT `fk_customer_postal_code`
44 FOREIGN KEY (`postal_code`)
45 REFERENCES `mydb`.`postal_code` (`id`)
46 ON DELETE NO ACTION
47 ON UPDATE NO ACTION)
48ENGINE = InnoDB;
49
50
51-- -----------------------------------------------------
52-- Table `mydb`.`product`
53-- -----------------------------------------------------
54CREATE TABLE IF NOT EXISTS `mydb`.`product` (
55 `id` INT(11) NOT NULL,
56 `description` VARCHAR(45) NOT NULL,
57 `price` DECIMAL(10,0) NOT NULL,
58 PRIMARY KEY (`id`),
59 UNIQUE INDEX `description_UNIQUE` (`description` ASC) VISIBLE)
60ENGINE = InnoDB;
61
62
63-- -----------------------------------------------------
64-- Table `mydb`.`invoice`
65-- -----------------------------------------------------
66CREATE TABLE IF NOT EXISTS `mydb`.`invoice` (
67 `id` INT(11) NOT NULL,
68 `date` DATE NOT NULL,
69 `total` DECIMAL(10,0) NOT NULL,
70 `customer` INT(11) NOT NULL,
71 PRIMARY KEY (`id`),
72 INDEX `fk_invoice_customer1_idx` (`customer` ASC) VISIBLE,
73 CONSTRAINT `fk_invoice_customer1`
74 FOREIGN KEY (`customer`)
75 REFERENCES `mydb`.`customer` (`id`)
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION)
78ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `mydb`.`invoice_item`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `mydb`.`invoice_item` (
85 `id` INT(11) NOT NULL,
86 `invoice` INT(11) NOT NULL,
87 `product_id` INT(11) NOT NULL,
88 `quantity` INT(11) NULL,
89 `amount` DECIMAL(10,0) NULL,
90 PRIMARY KEY (`id`),
91 INDEX `fk_invoice_item_invoice1_idx` (`invoice` ASC) VISIBLE,
92 INDEX `fk_invoice_item_product1_idx` (`product_id` ASC) VISIBLE,
93 CONSTRAINT `fk_invoice_item_invoice1`
94 FOREIGN KEY (`invoice`)
95 REFERENCES `mydb`.`invoice` (`id`)
96 ON DELETE NO ACTION
97 ON UPDATE NO ACTION,
98 CONSTRAINT `fk_invoice_item_product1`
99 FOREIGN KEY (`product_id`)
100 REFERENCES `mydb`.`product` (`id`)
101 ON DELETE NO ACTION
102 ON UPDATE NO ACTION)
103ENGINE = InnoDB;
104
105
106SET SQL_MODE=@OLD_SQL_MODE;
107SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
108SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;