mySQL- invalid default value

Question

Update: -- 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 mydb

-- Schema LibraryDB

CREATE SCHEMA IF NOT EXISTS `LibraryDB` DEFAULT CHARACTER SET latin1 ;
USE `LibraryDB` ;

-- Table LibraryDB.PUBLISHER

    CREATE TABLE IF NOT EXISTS `LibraryDB`.`PUBLISHER` (
      `Name` VARCHAR(60) NOT NULL,
      `Address` VARCHAR(60) NULL DEFAULT NULL,
      `Phone` INT(11) NULL DEFAULT NULL,
      PRIMARY KEY (`Name`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `LibraryDB`.`BOOK`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `LibraryDB`.`BOOK` (
  `Book_id` INT(11) NOT NULL,
  `Title` VARCHAR(60) NULL DEFAULT NULL,
  `Publisher_name` VARCHAR(60) NOT NULL,
  INDEX `publisher_name_idx` (`Publisher_name` ASC),
  PRIMARY KEY (`Book_id`),
  CONSTRAINT `publisher_name`
    FOREIGN KEY (`Publisher_name`)
    REFERENCES `LibraryDB`.`PUBLISHER` (`Name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

-- Table LibraryDB.BORROWER

CREATE TABLE IF NOT EXISTS `LibraryDB`.`BORROWER` (
  `Card_no` INT(11) NOT NULL,
  `Name` VARCHAR(25) NULL DEFAULT NULL,
  `Address` VARCHAR(255) NULL DEFAULT NULL,
  `Phone` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`Card_no`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

-- Table LibraryDB.LIBRARY_BRANCH

CREATE TABLE IF NOT EXISTS `LibraryDB`.`LIBRARY_BRANCH` (
  `Book_id` INT(11) NOT NULL,
  `Due_date` DATE NULL DEFAULT NULL,
  `Date_out` DATE NULL DEFAULT NULL,
  `Branch_id` INT(11) NOT NULL,
  `Card_no` INT(11) NOT NULL,
  PRIMARY KEY (`Branch_id`, `Book_id`, `Card_no`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

-- Table LibraryDB.Book_authors

CREATE TABLE IF NOT EXISTS `LibraryDB`.`Book_authors` (
  `Book_id` INT(11) NOT NULL,
  `Author_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Author_name`, `Book_id`),
  INDEX `Book_id_idx` (`Book_id` ASC),
  CONSTRAINT `Book_id`
    FOREIGN KEY (`Book_id`)
    REFERENCES `LibraryDB`.`BOOK` (`Book_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- Table LibraryDB.Book_copies

CREATE TABLE IF NOT EXISTS `LibraryDB`.`Book_copies` (
  `Book_id` INT(11) NOT NULL,
  `Branch_id` INT(11) NOT NULL,
  `No_of_copies` INT NULL,
  PRIMARY KEY (`Book_id`, `Branch_id`),
  INDEX `Branch_id_idx` (`Branch_id` ASC),
  CONSTRAINT `Book_id`
    FOREIGN KEY (`Book_id`)
    REFERENCES `LibraryDB`.`BOOK` (`Book_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Branch_id`
    FOREIGN KEY (`Branch_id`)
    REFERENCES `LibraryDB`.`LIBRARY_BRANCH` (`Branch_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- Table LibraryDB.Book_loans

CREATE TABLE IF NOT EXISTS `LibraryDB`.`Book_loans` (
  `Book_id` INT(11) NOT NULL,
  `Branch_id` INT(11) NOT NULL,
  `Card_no` INT(11) NOT NULL,
  `Date_out` DATE NULL,
  `Due_date` DATE NULL,
  PRIMARY KEY (`Book_id`, `Branch_id`, `Card_no`),
  INDEX `Branch_id_idx` (`Branch_id` ASC),
  INDEX `Card_no_idx` (`Card_no` ASC),
  CONSTRAINT `Book_id`
    FOREIGN KEY (`Book_id`)
    REFERENCES `LibraryDB`.`BOOK` (`Book_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Branch_id`
    FOREIGN KEY (`Branch_id`)
    REFERENCES `LibraryDB`.`LIBRARY_BRANCH` (`Branch_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Card_no`
    FOREIGN KEY (`Card_no`)
    REFERENCES `LibraryDB`.`BORROWER` (`Card_no`)
    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;

Executing SQL script in server ERROR: Error 1215: Cannot add foreign key constraint SQL Code:

-- Table LibraryDB.Book_copies

    CREATE TABLE IF NOT EXISTS `LibraryDB`.`Book_copies` (
      `Book_id` INT(11) NOT NULL,
      `Branch_id` INT(11) NOT NULL,
      `No_of_copies` INT NULL,
      PRIMARY KEY (`Book_id`, `Branch_id`),
      INDEX `Branch_id_idx` (`Branch_id` ASC),
      CONSTRAINT `Book_id`
        FOREIGN KEY (`Book_id`)
        REFERENCES `LibraryDB`.`BOOK` (`Book_id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `Branch_id`
        FOREIGN KEY (`Branch_id`)
        REFERENCES `LibraryDB`.`LIBRARY_BRANCH` (`Branch_id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

SQL script execution finished: statements: 10 succeeded, 1 failed

Specifying the default value as NULL directly after stating NOT NULL seems like an odd choice. Take out the NOT NULL and see if it works. — Steve, Jan 26 '20 at 19:31
Alright I have done that but now I'm getting a new error with the foreign key. I've double checked and can't find anything wrong with the reference. — user12787031, Jan 26 '20 at 21:57
Run through this checklist and see if you can find anything that helps: https://www.percona.com/blog/2017/04/06/dealing-mysql-error-code-1215-cannot-add-foreign-key-constraint/ — Steve, Jan 26 '20 at 22:20

mySQL- invalid default value

0 Answers0