I have 3 tables ,StudentInfos,LectureInfos, LectureStudentInfos . I have added some info into them. LectureStudentInfos includes 2 Foreign Keys from other 2 tables. Simply i created many-to-many relation between them. Now i need to add a row into LectureStudentInfos to be able to enter info about which student are selected which subject. Or which lectured selected by which students. But in this way im having an error.. What am i mıssing here?
CREATE DATABASE semesterDB;
CREATE TABLE StudentInfos(StudentId INT NOT NULL AUTO_INCREMENT, studentName VARCHAR(100) NOT NULL, PRIMARY KEY ( StudentId ));
INSERT INTO StudentInfos (studentName) VALUES ("Didi"), ("Steve"), ("Jasmin"), ("Laura"), ("Nancy"), ("Jordan"), ("Matt"), ("Katie"), ("Rose"), ("Lily");
CREATE TABLE LectureInfos(LecturetId INT NOT NULL AUTO_INCREMENT, lectureName VARCHAR(100) NOT NULL, lectureCredits CHAR(10) NOT NULL, PRIMARY KEY ( LecturetId ));
INSERT INTO LectureInfos (lectureName, lectureCredits) VALUES ("Quality management and quality management tools", "6"), ("Business Intelligence", "3"), ("Investment / financing and operational controlling", "3"), ("Application field health or trade or automotive industry", "6"), ("Creative negotiation and English", "6"), ("Corporate project, project and process controlling", "6");
CREATE TABLE LectureStudentInfos (
StudentId int NOT NULL,
LecturetId int NOT NULL,
FOREIGN KEY (StudentId) REFERENCES StudentInfos(StudentId), FOREIGN KEY (LecturetId) REFERENCES LectureInfos(LecturetId));
INSERT INTO LectureStudentInfos (StudentId, LecturetId) VALUES (1, 1), (1,2), (1,3), (2, 1), (2,2), (3,3),(3, 1), (3,2), (4,3),(4, 1), (4,2), (4,4), (4, 5), (4,6), (5,1),(5, 5), (6,2), (6,3),(6,5), (7,3), (7, 1), (7,2), (8,3),(8, 1), (8,2), (9,3),(9, 1), (9,2), (9,4), (9, 5), (10,6), (10,1);
Thanks to @PaulSpiegel
CREATE TABLE StudentInfos(StudentId INT NOT NULL AUTO_INCREMENT, studentName VARCHAR(100) NOT NULL, PRIMARY KEY ( StudentId ));
INSERT INTO StudentInfos (studentName) VALUES ("Didi"), ("Steve"), ("Jasmin"), ("Laura"), ("Nancy"), ("Jordan"), ("Matt"), ("Katie"), ("Rose"), ("Lily");
CREATE TABLE LectureInfos(LecturetId INT NOT NULL AUTO_INCREMENT, lectureName VARCHAR(100) NOT NULL, lectureCredits CHAR(10) NOT NULL, PRIMARY KEY ( LecturetId ));
INSERT INTO LectureInfos (lectureName, lectureCredits) VALUES ("Quality management and quality management tools", "6"), ("Business Intelligence", "3"), ("Investment / financing and operational controlling", "3"), ("Application field health or trade or automotive industry", "6"), ("Creative negotiation and English", "6"), ("Corporate project, project and process controlling", "6");
CREATE TABLE LectureStudentInfos (
StudentId int NOT NULL,
LecturetId int NOT NULL,
PRIMARY KEY (StudentId, LecturetId),
FOREIGN KEY (StudentId) REFERENCES StudentInfos(StudentId),
FOREIGN KEY (LecturetId) REFERENCES LectureInfos(LecturetId)
);
INSERT INTO LectureStudentInfos (StudentId, LecturetId) VALUES (1, 1), (1,2), (1,3), (2, 1), (2,2), (3,3),(3, 1), (3,2), (4,3),(4, 1), (4,2), (4,4), (4, 5), (4,6), (5,1),(5, 5), (6,2), (6,3),(6,5), (7,3), (7, 1), (7,2), (8,3),(8, 1), (8,2), (9,3),(9, 1), (9,2), (9,4), (9, 5), (10,6), (10,1):