K9r8jX1u

1CREATE TABLE IF NOT EXISTS Student (StudentId VARCHAR(6) NOT NULL, StudentName VARCHAR(250) NOT NULL, DegreeScheme VARCHAR(250) NOT NULL, PRIMARY KEY (StudentId), FOREIGN KEY (StudentId) REFERENCES Registered(StudentId) ON DELETE CASCADE ON UPDATE CASCADE);
2CREATE TABLE IF NOT EXISTS Module (ModuleId VARCHAR(6) NOT NULL, ModuleName VARCHAR(250) NOT NULL, Credits INTEGER(3) NOT NULL, PRIMARY KEY (ModuleId), FOREIGN KEY (ModuleId) REFERENCES Registered(ModuleId) ON DELETE CASCADE ON UPDATE CASCADE);
3CREATE TABLE IF NOT EXISTS Staff (StaffId VARCHAR(6) NOT NULL, StaffName VARCHAR(250) NOT NULL, Grade VARCHAR(100) NOT NULL, PRIMARY KEY (StaffId), FOREIGN KEY (StaffId) REFERENCES Teaches(StaffId) ON DELETE CASCADE ON UPDATE CASCADE);
4CREATE TABLE IF NOT EXISTS Registered (StudentId VARCHAR(6) NOT NULL, ModuleId VARCHAR(6) NOT NULL, PRIMARY KEY (StudentId,ModuleId), FOREIGN KEY (StudentId) REFERENCES Student(StudentId) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(ModuleId) REFERENCES Module(ModuleId) ON DELETE CASCADE ON UPDATE CASCADE);
5CREATE TABLE IF NOT EXISTS Teaches (StaffId VARCHAR(6) NOT NULL, ModuleId VARCHAR(6) NOT NULL, PRIMARY KEY (StaffId,ModuleId), FOREIGN KEY (StaffId) REFERENCES Staff(StaffId) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(ModuleId) REFERENCES Module(ModuleId) ON DELETE CASCADE ON UPDATE CASCADE);
6INSERT INTO Student VALUES('S10345', 'John Smith', 'BSc Computer Science');
7INSERT INTO Student VALUES('S10346', 'Sian Evans', 'BSc Computer Science');
8INSERT INTO Student VALUES('S10347', 'Sean Crossan', 'BSc Elec Engineering');
9INSERT INTO Student VALUES('S10348', 'Siobhan McChristy', 'BSc Mathematics');
10INSERT INTO Module VALUES('CS101', 'introduction to Computing', '10');
11INSERT INTO Module VALUES('CS203', 'Data Structures and Algorithms', '10');
12INSERT INTO Module VALUES('CS204', 'Computer Architecture', '10');
13INSERT INTO Module VALUES('M101', 'Mathematics I', '20'); 
14INSERT INTO Staff VALUES('E10010', 'Jim O Donovan', 'Senior Lecturer');
15INSERT INTO Staff VALUES('E10011', 'Neil Batchelor', 'Reader');
16INSERT INTO Staff VALUES('E10012', 'Steve Marriott', 'Lecturer');
17INSERT INTO Registered VALUES('S10345', 'CS101');
18INSERT INTO Registered VALUES('S10346', 'CS203');
19INSERT INTO Registered VALUES('S10346', 'CS204');
20INSERT INTO Registered VALUES('S10347', 'CS204');
21INSERT INTO Registered VALUES('S10348', 'M101');
22INSERT INTO Registered VALUES('S10348', 'C101');
23INSERT INTO Teaches VALUES('E10010', 'CS101');
24INSERT INTO Teaches VALUES('E10011', 'CS203');
25INSERT INTO Teaches VALUES('E10012', 'CS204');
26INSERT INTO Teaches VALUES('E10010', 'CS204');
27INSERT INTO Teaches VALUES('E10011', 'M101');
28INSERT INTO Teaches VALUES('E10011', 'C101');
29
30
31Output:
32(apart from several duplicate warnings which i ignore i know they are not inserted twice)
33
34
35Error code 1452, SQL state 23000: Cannot add or update a child row: a foreign key constraint fails (`testdatabase`.`registered`, CONSTRAINT `registered_ibfk_2` FOREIGN KEY (`ModuleId`) REFERENCES `module` (`ModuleId`) ON DELETE CASCADE ON UPDATE CASCADE)
36Line 22, column 1
37
38
39Error code 1452, SQL state 23000: Cannot add or update a child row: a foreign key constraint fails (`testdatabase`.`teaches`, CONSTRAINT `teaches_ibfk_2` FOREIGN KEY (`ModuleId`) REFERENCES `module` (`ModuleId`) ON DELETE CASCADE ON UPDATE CASCADE)
40Line 28, column 1