 |
How to insert Data INTO multiple Table using foreign KEY
|
|
|
|
TRIGGERS
HOW TO INSERT DATA INTO MULTIPLE TABLE USING FOREIGN KEY
Моя база данных
-- MySQL Script generated by MySQL Workbench
-- 05/26/17 09: 34: 22
-- Model: New Model Version: 1. 0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL, ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema TEMP_COMPANY
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema TEMP_COMPANY
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `TEMP_COMPANY`;
USE `TEMP_COMPANY`;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Types_of_document`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Types_of_document` (
`idType_of_document` INT NOT NULL AUTO_INCREMENT,
`Type_of_document` VARCHAR(45) NOT NULL COMMENT '- Служебная записка\n',
PRIMARY KEY (`idType_of_document`),
UNIQUE INDEX `Type_of_document_UNIQUE` (`Type_of_document` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Groups_of_document`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Groups_of_document` (
`idGroup_of_document` INT NOT NULL AUTO_INCREMENT,
`Groups_of_document` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idGroup_of_document`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Codes_of_document_by_GOST_34. 201_89`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Codes_of_document_by_GOST_34. 201_89` (
`idCode_of_document_by_GOST_34. 201_89` INT NOT NULL AUTO_INCREMENT,
`Code` VARCHAR(3) NOT NULL,
`Class_of_document` VARCHAR(45) NOT NULL,
`Purpose_of_document` VARCHAR(512) NOT NULL,
PRIMARY KEY (`idCode_of_document_by_GOST_34. 201_89`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Documents`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Documents` (
`№document` VARCHAR(45) NOT NULL,
`idType_of_document` INT NOT NULL,
`Theme_of_document` VARCHAR(45) NOT NULL,
`Date_of_creation_of_document` DATE NOT NULL,
|
|
|
`idGroup_of_document` INT NOT NULL,
`idCode_of_document_by_GOST_34. 201_89` INT NOT NULL,
PRIMARY KEY (`№document`, `idType_of_document`),
UNIQUE INDEX `Theme_of_document_UNIQUE` (`Theme_of_document` ASC),
INDEX `Type_of_document_idx` (`idType_of_document` ASC),
INDEX `Group_of_document_idx` (`idGroup_of_document` ASC),
INDEX `Code_of_document_idx` (`idCode_of_document_by_GOST_34. 201_89` ASC),
CONSTRAINT `Type_of_document(1)`
FOREIGN KEY (`idType_of_document`)
REFERENCES `TEMP_COMPANY`. `Types_of_document` (`idType_of_document`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Group_of_document(1)`
FOREIGN KEY (`idGroup_of_document`)
REFERENCES `TEMP_COMPANY`. `Groups_of_document` (`idGroup_of_document`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Code_of_document(1)`
FOREIGN KEY (`idCode_of_document_by_GOST_34. 201_89`)
REFERENCES `TEMP_COMPANY`. `Codes_of_document_by_GOST_34. 201_89` (`idCode_of_document_by_GOST_34. 201_89`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Organisations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Organisations` (
`idOrganisation` INT NOT NULL AUTO_INCREMENT COMMENT 'СУЩНОСТЬ \" ОРГАНИЗАЦИЯ\" \nВ роли первичного ключа был выбран суррогатный ключ, \nт. к. из всех атрибутов не было найдено \nнаиболее подходящего, \nкоторый бы удовлетворял критерию уникальности записи. ',
`Abbreviation_of_form_of_ownership` CHAR(5) NOT NULL,
`Name_of_company` CHAR(45) NOT NULL,
`Surname_of_owner` VARCHAR(45) NULL,
`Name_of_owner` VARCHAR(45) NOT NULL,
`Patronymic_of_owner` VARCHAR(45) NULL,
`Fax` CHAR(15) NOT NULL,
`Telephone` CHAR(15) NOT NULL,
`Email` CHAR(45) NOT NULL,
PRIMARY KEY (`idOrganisation`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Positions_of_employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Positions_of_employee` (
`idPosition_of_employee` INT NOT NULL,
`Name_of_position` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idPosition_of_employee`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Employees` (
`Clock_number` INT NOT NULL,
`Surname` CHAR(45) NULL,
`Name` CHAR(45) NOT NULL,
`Patronymic` CHAR(45) NULL,
`Gender` ENUM('Male', 'Female') NOT NULL,
`Contact_number` CHAR(15) NULL,
`idPosition_of_employee` INT NOT NULL,
`idOrganisation` INT NULL,
PRIMARY KEY (`Clock_number`),
UNIQUE INDEX `Clock_number_UNIQUE` (`Clock_number` ASC),
INDEX `position_idx` (`idPosition_of_employee` ASC),
INDEX `organization_idx` (`idOrganisation` ASC),
CONSTRAINT `organization(1)`
FOREIGN KEY (`idOrganisation`)
|
|
|
REFERENCES `TEMP_COMPANY`. `Organisations` (`idOrganisation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `position(1)`
FOREIGN KEY (`idPosition_of_employee`)
REFERENCES `TEMP_COMPANY`. `Positions_of_employee` (`idPosition_of_employee`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Universities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Universities` (
`idUniversity` INT NOT NULL AUTO_INCREMENT,
`University` VARCHAR(255) NOT NULL,
PRIMARY KEY (`idUniversity`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Class_of_engine`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Class_of_engine` (
`idClass_of_engine` INT NOT NULL COMMENT 'idclass_of_engine',
`rus_name` VARCHAR(45) NOT NULL,
`de_name` VARCHAR(45) NULL,
`eng_name` VARCHAR(45) NULL,
`fr_name` VARCHAR(45) NULL,
`description` VARCHAR(45) NULL,
`comment` VARCHAR(45) NULL,
PRIMARY KEY (`idClass_of_engine`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Types_of_production`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Types_of_production` (
`idType_of_production` INT NOT NULL,
`name_of_type_of_production` CHAR(45) NOT NULL,
PRIMARY KEY (`idType_of_production`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Types_of_engine`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Types_of_engine` (
`idType_of_engine` INT NOT NULL AUTO_INCREMENT,
`idType_of_production` INT NOT NULL,
`Name_of_engine` VARCHAR(45) NULL,
`Product_line` VARCHAR(45) NULL,
`Version` VARCHAR(45) NULL,
PRIMARY KEY (`idType_of_engine`),
INDEX `Types_of_production_idx` (`idType_of_production` ASC),
CONSTRAINT `Types_of_production`
FOREIGN KEY (`idType_of_production`)
REFERENCES `TEMP_COMPANY`. `Types_of_production` (`idType_of_production`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Indexes_of_engine`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Indexes_of_engine` (
`idIndex_of_engine` INT NOT NULL AUTO_INCREMENT,
`name_of_index` CHAR(45) NOT NULL,
PRIMARY KEY (`idIndex_of_engine`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Technical_states_by_GOST_15467`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Technical_states_by_GOST_15467` (
`idTechnical_state_by_GOST_15467` INT NOT NULL,
`State` VARCHAR(45) NOT NULL,
`Description_of_state` VARCHAR(45) NULL,
PRIMARY KEY (`idTechnical_state_by_GOST_15467`),
UNIQUE INDEX `State_UNIQUE` (`State` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Engines`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Engines` (
`idNumber_of_engine` INT NOT NULL COMMENT 'number_of_engine',
`idType_of_engine` INT NOT NULL COMMENT 'АИ-222-25\n99М1\n99В\n99А\n39\n99\nВК 2500\nТНА 150\nСУ-33\n\n',
`idIndex_of_engine` INT NOT NULL COMMENT 'Индекс двигателя\nнапример: АЛ-31Ф\nАЛ-31ФН\nАИ-222-25',
`idClass_of_engine` INT NOT NULL,
|
|
|
`Manufacturer_organisation` INT NOT NULL COMMENT 'guarantor_organisation',
`Exploiting_organisation` INT NOT NULL,
`idTechnical_state_by_GOST_15467` INT NOT NULL,
`Date_of_manufacturing` DATE NOT NULL COMMENT 'date_of_manufacturing - Дата выпуска двигателя',
`Modification_of_engine` VARCHAR(45) NOT NULL COMMENT 'modification_of_engine - Модификация изделия',
`Working_life_of_engine` INT NOT NULL,
`Type_of_guarantee` ENUM('гарантийный', 'негарантийный') NOT NULL,
PRIMARY KEY (`idNumber_of_engine`, `idType_of_engine`),
INDEX `Manufacturer_idx` (`Manufacturer_organisation` ASC),
INDEX `exploiting_organization1_idx` (`Exploiting_organisation` ASC),
INDEX `index_of_engine_idx` (`idIndex_of_engine` ASC),
INDEX `type_of_engine_idx` (`idType_of_engine` ASC),
INDEX `Class_of_engine_idx` (`idClass_of_engine` ASC),
INDEX `idTechnical_state_by_GOST_15467_idx` (`idTechnical_state_by_GOST_15467` ASC),
CONSTRAINT `Manufacturer(1)`
FOREIGN KEY (`Manufacturer_organisation`)
REFERENCES `TEMP_COMPANY`. `Organisations` (`idOrganisation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `exploiting_organization(1)`
FOREIGN KEY (`Exploiting_organisation`)
REFERENCES `TEMP_COMPANY`. `Organisations` (`idOrganisation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Class_of_engine(1)`
FOREIGN KEY (`idClass_of_engine`)
REFERENCES `TEMP_COMPANY`. `Class_of_engine` (`idClass_of_engine`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `type_of_engine(1)`
FOREIGN KEY (`idType_of_engine`)
REFERENCES `TEMP_COMPANY`. `Types_of_engine` (`idType_of_engine`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `index_of_engine(1)`
FOREIGN KEY (`idIndex_of_engine`)
REFERENCES `TEMP_COMPANY`. `Indexes_of_engine` (`idIndex_of_engine`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `idTechnical_state_by_GOST_15467(1)`
FOREIGN KEY (`idTechnical_state_by_GOST_15467`)
REFERENCES `TEMP_COMPANY`. `Technical_states_by_GOST_15467` (`idTechnical_state_by_GOST_15467`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Types_of_unit`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Types_of_unit` (
`idCode_of_unit` CHAR(45) NOT NULL,
`Passport` VARCHAR(45) NOT NULL,
`Full_name_of_unit` VARCHAR(80) NOT NULL,
`Purpose_of_unit` VARCHAR(45) NOT NULL,
`Cerificate_of_damage` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idCode_of_unit`),
INDEX `Passport_idx` (`Passport` ASC),
INDEX `Certificate_of_damage_idx` (`Cerificate_of_damage` ASC),
CONSTRAINT `Passport`
FOREIGN KEY (`Passport`)
REFERENCES `TEMP_COMPANY`. `Documents` (`№document`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Certificate_of_damage`
FOREIGN KEY (`Cerificate_of_damage`)
REFERENCES `TEMP_COMPANY`. `Documents` (`№document`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Types_of_defect_by_GOST_15467`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Types_of_defect_by_GOST_15467` (
`idType_of_defect` INT NOT NULL AUTO_INCREMENT,
`Type_of_defect` VARCHAR(100) NOT NULL COMMENT 'Де',
`Description_of_defect` VARCHAR(512) NOT NULL,
|
|
|
PRIMARY KEY (`idType_of_defect`),
UNIQUE INDEX `Type_of_defect_UNIQUE` (`Type_of_defect` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Types_of_test`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Types_of_test` (
`idType_of_test` INT NOT NULL AUTO_INCREMENT,
`Name_of_test` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idType_of_test`),
UNIQUE INDEX `Name_of_test_UNIQUE` (`Name_of_test` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Units`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Units` (
`№Unit` INT NOT NULL,
`idCode_of_unit` CHAR(45) NOT NULL,
`idNumber_of_engine` INT NOT NULL,
`idType_of_engine` INT NOT NULL,
`Manufacturer_organisation` INT NOT NULL,
`Developer_organisation` INT NOT NULL,
`idTechnical_state_by_GOST_15467` INT NOT NULL,
`idType_of_test` INT NOT NULL,
`Unit_is_defective` TINYINT(1) NOT NULL DEFAULT 0,
`Date_of_unit_creation` DATE NOT NULL,
`Belonging` INT NOT NULL,
PRIMARY KEY (`№Unit`, `idCode_of_unit`),
INDEX `Organisation_idx` (`Manufacturer_organisation` ASC),
INDEX `Developer_organisation_idx` (`Developer_organisation` ASC),
INDEX `Engine_idx` (`idNumber_of_engine` ASC),
INDEX `type_of_engine_idx` (`idType_of_engine` ASC),
INDEX `type_of_test_idx` (`idType_of_test` ASC),
INDEX `idTechnical_state_by_GOST_15467_idx` (`idTechnical_state_by_GOST_15467` ASC),
INDEX `idCode_of_unit_idx` (`idCode_of_unit` ASC),
CONSTRAINT `idCode_of_unit(1)`
FOREIGN KEY (`idCode_of_unit`)
REFERENCES `TEMP_COMPANY`. `Types_of_unit` (`idCode_of_unit`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Manufacturer_organisation(1)`
FOREIGN KEY (`Manufacturer_organisation`)
REFERENCES `TEMP_COMPANY`. `Organisations` (`idOrganisation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Developer_organisation(1)`
FOREIGN KEY (`Developer_organisation`)
REFERENCES `TEMP_COMPANY`. `Organisations` (`idOrganisation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Engine(1)`
FOREIGN KEY (`idNumber_of_engine`)
REFERENCES `TEMP_COMPANY`. `Engines` (`idNumber_of_engine`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `type_of_engine(1)`
FOREIGN KEY (`idType_of_engine`)
REFERENCES `TEMP_COMPANY`. `Types_of_engine` (`idType_of_engine`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `type_of_test(1)`
FOREIGN KEY (`idType_of_test`)
REFERENCES `TEMP_COMPANY`. `Types_of_test` (`idType_of_test`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `idTechnical_state_by_GOST_15467(1)`
FOREIGN KEY (`idTechnical_state_by_GOST_15467`)
REFERENCES `TEMP_COMPANY`. `Technical_states_by_GOST_15467` (`idTechnical_state_by_GOST_15467`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Types_of_failure_by_GOST_15467`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Types_of_failure_by_GOST_15467` (
`idType_of_failure` INT NOT NULL AUTO_INCREMENT,
`Type_of_failure` VARCHAR(100) NOT NULL,
`Description_of_failure` VARCHAR(512) NOT NULL,
PRIMARY KEY (`idType_of_failure`),
UNIQUE INDEX `Type_of_failure_UNIQUE` (`Type_of_failure` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Types_of_technical_service`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Types_of_technical_service` (
`ID` INT NOT NULL,
`Type_of_technical_service` VARCHAR(45) NOT NULL,
`Description` VARCHAR(512) NOT NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Groups_of_defect_place_in_engine`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Groups_of_defect_place_in_engine` (
`idGroup_of_defect_place_in_engine` INT NOT NULL AUTO_INCREMENT COMMENT 'Группа 1 - Компрессор\nГруппа 7 - Маслосистема\nГруппа 8 - Топливная система\nГруппа 9 - Электрооборудование (ЦРД-99)\nГруппа 16 - РС\nПРОЧИЕ\n',
|
|
|
`Placement` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idGroup_of_defect_place_in_engine`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Classifications_of_unit_defect`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Classifications_of_unit_defect` (
`idClassification_of_unit_defect` INT NOT NULL AUTO_INCREMENT,
`name_of_classification_of_unit_defect` CHAR(100) NOT NULL,
`Name_of_document_for_classification_of_unit_defect` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idClassification_of_unit_defect`),
INDEX `Name_of_document_for_classification_of_unit_defect1_idx` (`Name_of_document_for_classification_of_unit_defect` ASC),
CONSTRAINT `Name_of_document_for_classification_of_unit_defect(1)`
FOREIGN KEY (`Name_of_document_for_classification_of_unit_defect`)
REFERENCES `TEMP_COMPANY`. `Documents` (`№document`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Defects`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Defects` (
`idDefect` INT NOT NULL AUTO_INCREMENT,
`idType_of_defect` INT NOT NULL,
`idClassification_of_unit_defect` INT NOT NULL,
`idGroup_of_defect_place_in_engine` INT NOT NULL,
`Image_of_defect` LONGBLOB NULL,
`Description_of_defect` VARCHAR(1024) NOT NULL,
`Measures_for_elimination_defect` VARCHAR(1024) NOT NULL,
`Method_for_elimination_defect` VARCHAR(1024) NOT NULL,
`Result_of_investigation` VARCHAR(1024) NOT NULL,
`Date_of_appearance` DATE NOT NULL,
`First_defect_appearence_since_using_of_unit` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idDefect`),
INDEX `idType_of_defect_idx` (`idType_of_defect` ASC),
INDEX `idGroup_of_defect_place_in_engine_idx` (`idGroup_of_defect_place_in_engine` ASC),
INDEX `idClassification_of_unit_defect_idx` (`idClassification_of_unit_defect` ASC),
CONSTRAINT `idType_of_defect(1)`
FOREIGN KEY (`idType_of_defect`)
REFERENCES `TEMP_COMPANY`. `Types_of_defect_by_GOST_15467` (`idType_of_defect`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `idGroup_of_defect_place_in_engine(1)`
FOREIGN KEY (`idGroup_of_defect_place_in_engine`)
REFERENCES `TEMP_COMPANY`. `Groups_of_defect_place_in_engine` (`idGroup_of_defect_place_in_engine`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `idClassification_of_unit_defect(1)`
FOREIGN KEY (`idClassification_of_unit_defect`)
REFERENCES `TEMP_COMPANY`. `Classifications_of_unit_defect` (`idClassification_of_unit_defect`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Employees_has_Documents`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Employees_has_Documents` (
`Employees_Clock_number` INT NOT NULL,
`Documents_№document` VARCHAR(45) NOT NULL,
`Documents_idType_of_document` INT NOT NULL,
PRIMARY KEY (`Employees_Clock_number`, `Documents_№document`, `Documents_idType_of_document`),
INDEX `fk_Employees_has_Documents_Documents1_idx` (`Documents_№document` ASC, `Documents_idType_of_document` ASC),
INDEX `fk_Employees_has_Documents_Employees1_idx` (`Employees_Clock_number` ASC),
CONSTRAINT `fk_Employees_has_Documents_Employees1`
FOREIGN KEY (`Employees_Clock_number`)
REFERENCES `TEMP_COMPANY`. `Employees` (`Clock_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Employees_has_Documents_Documents1`
FOREIGN KEY (`Documents_№document`, `Documents_idType_of_document`)
REFERENCES `TEMP_COMPANY`. `Documents` (`№document`, `idType_of_document`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Employees_has_Universities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Employees_has_Universities` (
`Employees_Clock_number` INT NOT NULL,
`Universities_idUniversity` INT NOT NULL,
`Date_of_enrollment` DATE NULL,
`Date_of_graduation` DATE NULL,
PRIMARY KEY (`Employees_Clock_number`, `Universities_idUniversity`),
INDEX `fk_Employees_has_Universities_Universities1_idx` (`Universities_idUniversity` ASC),
INDEX `fk_Employees_has_Universities_Employees1_idx` (`Employees_Clock_number` ASC),
CONSTRAINT `fk_Employees_has_Universities_Employees1`
FOREIGN KEY (`Employees_Clock_number`)
REFERENCES `TEMP_COMPANY`. `Employees` (`Clock_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Employees_has_Universities_Universities1`
FOREIGN KEY (`Universities_idUniversity`)
REFERENCES `TEMP_COMPANY`. `Universities` (`idUniversity`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Units_has_Defects`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Units_has_Defects` (
`Units_№Unit` INT NOT NULL,
`Units_Code_of_unit` VARCHAR(45) NOT NULL,
`Defects_idDefect` INT NOT NULL,
`Date_of_unit_removal` DATE NOT NULL,
PRIMARY KEY (`Units_№Unit`, `Units_Code_of_unit`, `Defects_idDefect`),
INDEX `fk_Units_has_Defects_Defects1_idx` (`Defects_idDefect` ASC),
INDEX `fk_Units_has_Defects_Units1_idx` (`Units_№Unit` ASC, `Units_Code_of_unit` ASC),
CONSTRAINT `fk_Units_has_Defects_Units1`
FOREIGN KEY (`Units_№Unit`, `Units_Code_of_unit`)
REFERENCES `TEMP_COMPANY`. `Units` (`№Unit`, `idCode_of_unit`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Units_has_Defects_Defects1`
FOREIGN KEY (`Defects_idDefect`)
REFERENCES `TEMP_COMPANY`. `Defects` (`idDefect`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Names_of_failure`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Names_of_failure` (
`idName_of_failure` INT NOT NULL AUTO_INCREMENT,
`Name` CHAR(255) NOT NULL,
PRIMARY KEY (`idName_of_failure`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Failures`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Failures` (
`idFailures` INT NOT NULL AUTO_INCREMENT,
`idType_of_failure` INT NOT NULL,
`idType_of_technical_service` INT NOT NULL,
`idName_of_failure` INT NOT NULL,
`Failure_criterion` VARCHAR(45) NOT NULL,
`Failure_cause` VARCHAR(45) NOT NULL,
`Failure_effect` VARCHAR(45) NOT NULL,
`Failure_criticality` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idFailures`),
INDEX `idType_of_failure_idx` (`idType_of_failure` ASC),
INDEX `idType_of_technical_service_idx` (`idType_of_technical_service` ASC),
INDEX `idType_error_failure(1)_idx` (`idName_of_failure` ASC),
CONSTRAINT `idType_of_failure(1)`
FOREIGN KEY (`idType_of_failure`)
REFERENCES `TEMP_COMPANY`. `Types_of_failure_by_GOST_15467` (`idType_of_failure`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `idType_of_technical_service(1)`
FOREIGN KEY (`idType_of_technical_service`)
REFERENCES `TEMP_COMPANY`. `Types_of_technical_service` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `idName_of_failure(1)`
FOREIGN KEY (`idName_of_failure`)
REFERENCES `TEMP_COMPANY`. `Names_of_failure` (`idName_of_failure`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Units_has_Failures`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Units_has_Failures` (
`Units_№Unit` INT NOT NULL,
`Units_Code_of_unit` VARCHAR(45) NOT NULL,
`Failures_idFailures` INT NOT NULL,
PRIMARY KEY (`Units_№Unit`, `Units_Code_of_unit`, `Failures_idFailures`),
INDEX `fk_Units_has_Failures_Failures1_idx` (`Failures_idFailures` ASC),
INDEX `fk_Units_has_Failures_Units1_idx` (`Units_№Unit` ASC, `Units_Code_of_unit` ASC),
CONSTRAINT `fk_Units_has_Failures_Units1`
FOREIGN KEY (`Units_№Unit`, `Units_Code_of_unit`)
REFERENCES `TEMP_COMPANY`. `Units` (`№Unit`, `idCode_of_unit`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Units_has_Failures_Failures1`
FOREIGN KEY (`Failures_idFailures`)
REFERENCES `TEMP_COMPANY`. `Failures` (`idFailures`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `exhausting_fraction_of_fault_probability`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `exhausting_fraction_of_fault_probability` (
`ID` INT NOT NULL AUTO_INCREMENT COMMENT 'ДОЛЯ ИСЧЕРПАНИЯ ПОВРЕЖДАЕМОСТИ',
`№_engine` INT NOT NULL,
`exhausting_fraction_of_fault_probability1` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability2` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability3` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability4` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability5` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability6` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability7` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability8` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability9` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability10` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability11` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability12` DOUBLE NOT NULL,
`exhausting_fraction_of_fault_probability13` DOUBLE NOT NULL,
PRIMARY KEY (`ID`, `№_engine`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `fault_probability_of_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `fault_probability_of_details` (
`ID` INT NOT NULL AUTO_INCREMENT,
`№_engine` INT NOT NULL COMMENT 'ПОВРЕЖДАЕМОСТЬ ОСНОВНЫХ ДЕТАЛЕЙ',
`fault_probability_of_units1` DOUBLE NOT NULL,
`fault_probability_of_units2` DOUBLE NOT NULL,
`fault_probability_of_units3` DOUBLE NOT NULL,
`fault_probability_of_units4` DOUBLE NOT NULL,
`fault_probability_of_units5` DOUBLE NOT NULL,
`fault_probability_of_units6` DOUBLE NOT NULL,
`fault_probability_of_units7` DOUBLE NOT NULL,
`fault_probability_of_units8` DOUBLE NOT NULL,
`fault_probability_of_units9` DOUBLE NOT NULL,
`fault_probability_of_units10` DOUBLE NOT NULL,
`fault_probability_of_units11` DOUBLE NOT NULL,
`fault_probability_of_units12` DOUBLE NOT NULL,
`fault_probability_of_units13` DOUBLE NOT NULL,
PRIMARY KEY (`ID`, `№_engine`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `fraction_of_exhaustion_of_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `fraction_of_exhaustion_of_details` (
`ID` INT NOT NULL AUTO_INCREMENT COMMENT 'ДОЛЯ ИСЧЕРПАНИЯ РЕСУРСОВ ПО ЗАПУСКАМ, ПО ВИДАМ НАРАБОТКИ ДВИГАТЕЛЯ',
`№_engine` INT NOT NULL COMMENT 'ДОЛЯ ИСЧЕРПАНИЯ РЕСУРСОВ ПО ЗАПУСКАМ, ПО ВИДАМ НАРАБОТКИ ДВИГАТЕЛЯ',
`fraction_of_exhaustion_of_units1` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units2` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units3` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units4` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units5` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units6` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units7` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units8` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units9` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units10` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units11` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units12` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units13` DOUBLE NOT NULL,
`fraction_of_exhaustion_of_units14` DOUBLE NOT NULL,
PRIMARY KEY (`ID`, `№_engine`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `stressing_cycle`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `stressing_cycle` (
`ID` INT NOT NULL AUTO_INCREMENT,
`№_engine` INT NOT NULL COMMENT 'ЦИКЛЫ НАГРУЖЕНИЯ',
`№_digital_regulator` INT(11) NOT NULL,
`programme` VARCHAR(45) NOT NULL,
`n1vr` INT(11) NOT NULL,
`n2vr` INT(11) NOT NULL,
`n3vr` INT(11) NOT NULL,
`n1b` INT(11) NOT NULL,
`n2b` INT(11) NOT NULL,
`n3b` INT(11) NOT NULL,
`n1u` INT(11) NOT NULL,
`n2u` INT(11) NOT NULL,
`n3u` INT(11) NOT NULL,
`date` DATE NOT NULL,
`time` TIME NOT NULL,
`organization` INT NOT NULL,
PRIMARY KEY (`ID`, `№_engine`, `№_digital_regulator`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `TEMP_COMPANY`. `Users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TEMP_COMPANY`. `Users` (
`idUser` INT NOT NULL AUTO_INCREMENT,
`Login` VARCHAR(45) NOT NULL,
`Password` VARCHAR(45) NOT NULL,
`Clock_number` INT NOT NULL,
PRIMARY KEY (`idUser`),
INDEX `Clock_number(1)_idx` (`Clock_number` ASC),
CONSTRAINT `Clock_number(1)`
FOREIGN KEY (`Clock_number`)
REFERENCES `TEMP_COMPANY`. `Employees` (`Clock_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
|
|
|
