-- MySQL Script generated by MySQL Workbench -- Fri Jan 22 17:34:03 2021 -- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`base_calendars_exceptions` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`base_calendars_exceptions` ( `id` INT NOT NULL AUTO_INCREMENT, `id_base_calendar_exception` INT NULL, `name` VARCHAR(254) NULL, `date` DATETIME NULL, `start_hour` DECIMAL(4,2) NULL, `end_hour` DECIMAL(4,2) NULL, `free_week_days` SMALLINT(1) NULL, `user_id` INT NULL, PRIMARY KEY (`id`), INDEX `ID_idx` (`id_base_calendar_exception` ASC) VISIBLE, CONSTRAINT `ID_1` FOREIGN KEY (`id_base_calendar_exception`) REFERENCES `mydb`.`base_calendars_settings` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`base_calendars_generated` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`base_calendars_generated` ( `ID` INT NOT NULL AUTO_INCREMENT, `id_based_calendar_generated` INT NULL, `c_date` DATETIME NULL, `star_hour` DECIMAL(4,2) NULL, `end_hour` DECIMAL(4,2) NULL, `working_day` SMALLINT(1) NOT NULL, `day` SMALLINT(2) NULL, `day_name` VARCHAR(50) NULL, `week` SMALLINT(2) NULL, `month` SMALLINT(2) NULL, `month_name` VARCHAR(50) NULL, `quarter` SMALLINT(1) NULL, `year` SMALLINT(4) NULL, `day_of_year` SMALLINT(3) NULL, PRIMARY KEY (`ID`, `working_day`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`base_calendars_settings` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`base_calendars_settings` ( `ID` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(254) NOT NULL, `is_blocked` SMALLINT(1) NOT NULL, `is_default` SMALLINT(1) NOT NULL, `start_date` DATETIME NULL, `default_start_hour` DECIMAL(4,2) NOT NULL, `default_end_hour` DECIMAL(4,2) NOT NULL, `first_day` SMALLINT(1) NULL, `user_id` INT NULL, PRIMARY KEY (`ID`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`resource_calendar_settings` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`resource_calendar_settings` ( `ID` INT NOT NULL AUTO_INCREMENT COMMENT 'primary key', `user_id` VARCHAR(45) NULL COMMENT 'ID of employee', `name` VARCHAR(50) NULL COMMENT 'employee name', `surname` VARCHAR(100) NULL COMMENT 'employee surname', `type_of_leave` VARCHAR(100) NULL COMMENT 'time off type: vacation, sick leave, absence, other', `start_time` DATETIME NULL COMMENT 'time off start date', `end_time` DATETIME NULL COMMENT 'time off end date', `comments` VARCHAR(256) NULL COMMENT 'comment about time off', PRIMARY KEY (`ID`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`shift_calendar` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`shift_calendar` ( `ID` INT NOT NULL AUTO_INCREMENT, `user_id` INT NULL COMMENT 'od of employee', `name` VARCHAR(50) NULL COMMENT 'name of employee', `surname` VARCHAR(100) NULL COMMENT 'surname of employee', `start_time` DECIMAL(4,2) NULL COMMENT 'employee start time', `end_time` DECIMAL(4,2) NULL COMMENT 'employe end time', `operator_id` INT NOT NULL COMMENT 'Id of operator who made changes', `working_date` DATETIME NULL COMMENT 'datetime when employee work', `working_hours` DECIMAL(4,2) NULL COMMENT 'working hours per day', `is_weekend` SMALLINT(2) NULL, PRIMARY KEY (`ID`), INDEX `holiday_idx` (`is_weekend` ASC) VISIBLE, CONSTRAINT `holiday` FOREIGN KEY (`is_weekend`) REFERENCES `mydb`.`base_calendars_generated` (`working_day`) 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; Want to run the following sql but i got error Error Code: 1005. Can’t create table mydb.shift_calendar (errno: 150 "Foreign key constraint is incorrectly formed") Dont understand where is problem column is_weekend and working_day have the same type smalint(1)
