mf8QvDph

1-- MySQL Script generated by MySQL Workbench
2-- Сб 20 янв 2018 13:34:59
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='TRADITIONAL,ALLOW_INVALID_DATES';
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`.`departments`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`departments` (
24  `id` INT(6) NOT NULL,
25  `name` VARCHAR(60) NOT NULL,
26  PRIMARY KEY (`id`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`offices`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`offices` (
34  `name` VARCHAR(30) NOT NULL,
35  `salary` DECIMAL(8,2) NOT NULL,
36  PRIMARY KEY (`name`))
37ENGINE = InnoDB;
38
39
40-- -----------------------------------------------------
41-- Table `mydb`.`employees`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `mydb`.`employees` (
44  `id` INT(6) NOT NULL AUTO_INCREMENT,
45  `fio` VARCHAR(40) NOT NULL,
46  `inn` CHAR(12) NOT NULL,
47  `sex` CHAR(1) NOT NULL DEFAULT 'м',
48  `date_of_birth` DATE NOT NULL,
49  `date_of_employment` DATE NOT NULL,
50  `experience` DECIMAL(5,2) NULL DEFAULT 0,
51  `office` VARCHAR(30) NOT NULL,
52  `department` INT(6) NOT NULL,
53  PRIMARY KEY (`id`, `department`, `office`),
54  UNIQUE INDEX `INN_UNIQUE` (`inn` ASC),
55  INDEX `fk_employees_offices1_idx` (`office` ASC),
56  INDEX `fk_employees_departments1_idx` (`department` ASC),
57  CONSTRAINT `fk_employees_offices1`
58    FOREIGN KEY (`office`)
59    REFERENCES `mydb`.`offices` (`name`)
60    ON DELETE NO ACTION
61    ON UPDATE NO ACTION,
62  CONSTRAINT `fk_employees_departments1`
63    FOREIGN KEY (`department`)
64    REFERENCES `mydb`.`departments` (`id`)
65    ON DELETE NO ACTION
66    ON UPDATE NO ACTION)
67ENGINE = InnoDB;
68
69
70-- -----------------------------------------------------
71-- Table `mydb`.`children`
72-- -----------------------------------------------------
73CREATE TABLE IF NOT EXISTS `mydb`.`children` (
74  `id` INT(6) NOT NULL,
75  `fio` VARCHAR(40) NOT NULL,
76  `sex` CHAR(1) NOT NULL DEFAULT 'м',
77  `date_of_birth` DATE NOT NULL,
78  `parent` INT(6) NOT NULL,
79  PRIMARY KEY (`id`, `parent`),
80  INDEX `fk_children_employees1_idx` (`parent` ASC),
81  CONSTRAINT `fk_children_employees1`
82    FOREIGN KEY (`parent`)
83    REFERENCES `mydb`.`employees` (`id`)
84    ON DELETE NO ACTION
85    ON UPDATE NO ACTION)
86ENGINE = InnoDB;
87
88
89SET SQL_MODE=@OLD_SQL_MODE;
90SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
91SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;