MySql FK issues

Question

This is my first post on here. I am still very new to MySQL. I am having issues debugging some code involving Foreign Keys. Whenever I un-comment the foreign keys and try to run the code (using XAMPP and Netbeans 8.2) I get errors saying 'the foregin key constriant is incorrectly formed'? I have checked over the data types between the tables as well as the foreign key syntax and tried all kinds of things but I am all out of ideas (no it is not the commas, I have previously corrected that and the problem persists) and completely fed up with it. Why is this happening and how do I fix this? Thanks

DROP TABLE IF EXISTS Staff;
DROP TABLE IF EXISTS Subjects;
DROP TABLE IF EXISTS Results;
DROP TABLE IF EXISTS Students;


CREATE TABLE Students
(
    StudentID       INT NOT NULL,
    Forename        VARCHAR(20) NOT NULL,
    Surname         VARCHAR(20) NOT NULL,
    Gender          CHAR(1) NOT NULL,
    DateOfBirth     DATE NOT NULL,
    PRIMARY KEY (StudentID)
);


CREATE TABLE Results
(
    StudentID       INT NOT NULL,
    SubjectID       CHAR(6) NOT NULL,
    Result          INT,
    PRIMARY KEY (StudentID, SubjectID),
    FOREIGN KEY (StudentID)REFERENCES Students(StudentID)
    /*FOREIGN KEY (SubjectID)REFERENCES Subjects(SubjectID)*/
);


CREATE TABLE Subjects
(
    SubjectID       CHAR(6) NOT NULL,
    SubjectTitle    VARCHAR(50) NOT NULL,
    StaffID         INT NOT NULL,
    PRIMARY KEY (SubjectID)
    /*FOREIGN KEY (StaffID) REFERENCES Staff(StaffID)*/
);


CREATE TABLE Staff
(
    StaffID         INT NOT NULL,
    Forename        VARCHAR(20) NOT NULL,
    Surname         VARCHAR(20) NOT NULL,
    Post            VARCHAR(20) NOT NULL,
    PRIMARY KEY (StaffID)
);

You need a comma on the line before the foreign key constraint. — Gordon Linoff, Feb 23 '21 at 19:26
The order tables are created matters - a table has to exist before another can point to it as a FK. So you must create `Staff,Students` first, then `Subjects`, finally `Results`. — Michael Berkowski, Feb 23 '21 at 19:27
@GordonLinoff No that is not the solution. Please read the accompanying text where I exclusively mention the commas. Thanks — 13008620, Feb 23 '21 at 19:29
@13008620 . . . You also have to define a table before you can define a foreign key to it. — Gordon Linoff, Feb 23 '21 at 19:33
You might like this checklist for foreign keys: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Feb 23 '21 at 20:04

MySql FK issues

0 Answers0