SQL error: 1364 Field 'XXXX' doesn't have a default value

2
votes

So I have, table courses:

CREATE  TABLE IF NOT EXISTS `AppDziennik`.`courses` (
  `id_course` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(80) NOT NULL ,
  `teachers_id_teacher` INT NOT NULL ,
  `end_date` DATE NULL ,
  `about` VARCHAR(255) NULL ,
  `start_date` DATE NULL ,
  PRIMARY KEY (`id_course`) ,
  UNIQUE INDEX `id_course_UNIQUE` (`id_course` ASC) ,
  CONSTRAINT `fk_courses_teachers1`
    FOREIGN KEY (`teachers_id_teacher` )
    REFERENCES `AppDziennik`.`teachers` (`id_teacher` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

and second table

CREATE  TABLE IF NOT EXISTS `AppDziennik`.`teachers` (
  `id_teacher` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `surname` VARCHAR(45) NOT NULL ,
  `about` VARCHAR(255) NULL ,
  `id_class` INT NULL ,
  `rank` VARCHAR(45) NULL ,
  `logins_id_login` INT NOT NULL ,
  PRIMARY KEY (`id_teacher`) ,
  INDEX `fk_teachers_classes1_idx` (`id_class` ASC) ,
  INDEX `fk_teachers_logins1_idx` (`logins_id_login` ASC) ,
  CONSTRAINT `fk_teachers_classes1`
    FOREIGN KEY (`id_class` )
    REFERENCES `AppDziennik`.`classes` (`id_class` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_teachers_logins1`
    FOREIGN KEY (`logins_id_login` )
    REFERENCES `AppDziennik`.`logins` (`id_login` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

When I make that insert

insert into appdziennik.courses (name, id_teacher, about, start_date, end_date) values ("Math",'7',"Math",'2013-08-01','2014-06-29');

I get that error:

Error Code: 1364. Field 'teachers_id_teacher' doesn't have a default value

Where I made mistake? How i could fix it.

2
mysqlsqlforeign-keys
The column name in courses appears to be teachers_id_teacher rather than id_teacher - typo in this question or type in your code? – Damien_The_Unbeliever
I trust too much into autogenerate code my MySQL WorkBench. Its calls columns like this(teachers_id_teacher) when i made. Anyway THX – Aku

2 Answers

2
votes

If you're sending an INSERT to table courses, there is no field id_teacher (you want teachers_id_teacher). Since you're using a foreign key, that key must exist in table teachers before you INSERT a record into table courses. Also, you should only be using backticks (`) and single quotes (') in all your statements.

1
votes

you are trying to insert into the courses table for the column id_teacher a value but your not inserting a value for the column teachers_id_teacher. so I would propose to make the statement as followed:

insert into appdziennik.courses (name, teachers_id_teacher, about, start_date, end_date) values ("Math",'7',"Math",'2013-08-01','2014-06-29');

Sarajog