I am new to mysql and I am trying create a gradebook db to keep track of grades for a certain class. I am using Mysql workbench and here is my code:
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';
DROP SCHEMA IF EXISTS nj1368843 ;
CREATE SCHEMA IF NOT EXISTS nj1368843 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE nj1368843 ;
-- Table nj1368843.Users
DROP TABLE IF EXISTS nj1368843.Users ;
CREATE TABLE IF NOT EXISTS nj1368843.Users (
idUsers INT NOT NULL AUTO_INCREMENT ,
UserName VARCHAR(45) NOT NULL ,
pw VARCHAR(45) NOT NULL ,
PRIMARY KEY (idUsers, UserName, pw) )
ENGINE = InnoDB;
INSERT INTO nj1368843.Users (UserName, pw) VALUES ('njack2', '123');
-- Table nj1368843.Teachers
DROP TABLE IF EXISTS nj1368843.Teachers ;
CREATE TABLE IF NOT EXISTS nj1368843.Teachers (
idTeachers INT NOT NULL ,
Lname VARCHAR(45) NULL ,
Fname VARCHAR(45) NULL ,
Users_idUsers INT NOT NULL ,
Users_pw VARCHAR(45) NOT NULL ,
PRIMARY KEY (idTeachers) ,
INDEX fk_Teachers_Users1 (Users_idUsers ASC, Users_pw ASC) ,
CONSTRAINT fk_Teachers_Users1
FOREIGN KEY (`Users_idUsers` , `Users_pw` )
REFERENCES `nj1368843`.`Users` (`idUsers` , `UserName` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INSERT INTO nj1368843.Teachers (idTeachers, Lname, Fname, Users_idUsers, Users_pw) VALUES (105, 'Stacey', 'Sheila', '1', '123');
-- Table nj1368843.Schedule
DROP TABLE IF EXISTS nj1368843.Schedule ;
CREATE TABLE IF NOT EXISTS nj1368843.Schedule (
course_id INT NOT NULL ,
Semester VARCHAR(45) NULL ,
Year YEAR NULL ,
Teachers_idTeachers INT NOT NULL ,
PRIMARY KEY (course_id) ,
INDEX fk_Grades_Teachers1 (Teachers_idTeachers ASC) ,
CONSTRAINT fk_Grades_Teachers1
FOREIGN KEY (`Teachers_idTeachers` )
REFERENCES `nj1368843`.`Teachers` (`idTeachers` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table nj1368843.Assignments
DROP TABLE IF EXISTS nj1368843.Assignments ;
CREATE TABLE IF NOT EXISTS nj1368843.Assignments (
idAssignments INT NOT NULL ,
Assignment 1 INT NULL ,
AVG_Grade INT(11) NULL ,
Schedule_course_id INT NOT NULL ,
PRIMARY KEY (idAssignments) ,
INDEX fk_Assignments_Schedule1 (Schedule_course_id ASC) ,
CONSTRAINT fk_Assignments_Schedule1
FOREIGN KEY (`Schedule_course_id` )
REFERENCES `nj1368843`.`Schedule` (`course_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table nj1368843.Student
DROP TABLE IF EXISTS nj1368843.Student ;
CREATE TABLE IF NOT EXISTS nj1368843.Student (
idStudent INT NOT NULL ,
lname VARCHAR(45) NULL ,
fname VARCHAR(45) NULL ,
Schedule_course_id INT NOT NULL ,
Users_idUsers INT NOT NULL ,
Users_pw VARCHAR(45) NOT NULL ,
Assignments_idAssignments INT NOT NULL ,
PRIMARY KEY (idStudent) ,
INDEX fk_Student_Schedule1 (Schedule_course_id ASC) ,
INDEX fk_Student_Users1 (Users_idUsers ASC, Users_pw ASC) ,
INDEX fk_Student_Assignments1 (Assignments_idAssignments ASC) ,
CONSTRAINT fk_Student_Schedule1
FOREIGN KEY (`Schedule_course_id` )
REFERENCES `nj1368843`.`Schedule` (`course_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_Student_Users1
FOREIGN KEY (`Users_idUsers` , `Users_pw` )
REFERENCES `nj1368843`.`Users` (`idUsers` , `UserName` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_Student_Assignments1
FOREIGN KEY (`Assignments_idAssignments` )
REFERENCES `nj1368843`.`Assignments` (`idAssignments` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table nj1368843.Classes
DROP TABLE IF EXISTS nj1368843.Classes ;
CREATE TABLE IF NOT EXISTS nj1368843.Classes (
cid INT NOT NULL ,
Name VARCHAR(45) NULL ,
Schedule_course_id INT NOT NULL ,
PRIMARY KEY (cid) ,
INDEX fk_Classes_Schedule1 (Schedule_course_id ASC) ,
CONSTRAINT fk_Classes_Schedule1
FOREIGN KEY (`Schedule_course_id` )
REFERENCES `nj1368843`.`Schedule` (`course_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- Table nj1368843.Teachers
DROP TABLE IF EXISTS nj1368843.Teachers ;
CREATE TABLE IF NOT EXISTS nj1368843.Teachers (
idTeachers INT NOT NULL ,
Lname VARCHAR(45) NULL ,
Fname VARCHAR(45) NULL ,
Users_idUsers INT NOT NULL ,
Users_pw VARCHAR(45) NOT NULL ,
PRIMARY KEY (idTeachers) ,
INDEX fk_Teachers_Users1 (Users_idUsers ASC, Users_pw ASC) ,
CONSTRAINT fk_Teachers_Users1
FOREIGN KEY (`Users_idUsers` , `Users_pw` )
REFERENCES `nj1368843`.`Users` (`idUsers` , `UserName` )
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;
I generated this out of an erd diagram and I can't insert any information in the database because I get:
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (nj1368843.Teachers, CONSTRAINT fk_Teachers_Users1 FOREIGN KEY (Users_idUsers, Users_pw) REFERENCES Users (idUsers, UserName) ON DELETE NO ACTION ON UPDATE NO ACTION)
SQL Statement:
INSERT INTO nj1368843.Teachers (idTeachers, Lname, Fname, Users_idUsers, Users_pw) VALUES (105, 'Stacey', 'Sheila', 1, '123')
I tried everyone's ideas and looked over the code a million times and still can't find the problem. I can't insert into none of the tables for this db.help.