MySQL Referential Integrity Issue

MySQL

I'm having a lot of issues and don't really know where to look. I'm pretty sure it's a referential integrity issue and has something to do with the addition of the Professor table and the Foreign Key Constraints between it and the Section table.

I've narrowed the error down to the Enrolled table. It's the one that gives me "ERROR 1215 (HY000): Cannot add foreign key constraint" when I try an add it. Remove the Professor, QualToTeach table and the Foreign Key constraints to professor from Section and it works beautifully.

Any help?

Here's my code:

CREATE TABLE Student (
    StudentID INT,
    Fname VARCHAR(30),
    Lname VARCHAR(30),
    DOB DATE,
    Major VARCHAR(10),
    PRIMARY KEY (StudentID)) ENGINE=InnoDB;

CREATE TABLE Professor (
    Emp_no INT,
    FName VARCHAR(10),
    LName VARCHAR(10),
    DeptCode VARCHAR(2),
    PRIMARY KEY (Emp_no)) ENGINE=InnoDB;

CREATE TABLE Phone (
    StudentID INT,
    PNumber VARCHAR(20),
    Type VARCHAR(5),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE
    ) ENGINE=InnoDB;

CREATE TABLE Class (
    ClassID VARCHAR(5),
    NoCredits INT,
    Description VARCHAR(30),
    Requisite1 VARCHAR(6),
    Requisite2 VARCHAR(6),
    Requisite3 VARCHAR(6),
    PRIMARY KEY (ClassID)) ENGINE=InnoDB;

CREATE TABLE Section (
    ClassID VARCHAR(5),
    SecNo VARCHAR(10),
    Semester VARCHAR(4),
    ClassRoom VARCHAR(6),
    TimeOffered VARCHAR(20),
    Emp_no INT,
    PRIMARY KEY (ClassID, Emp_no, SecNo, Semester),
    FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
    FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)
    ) ENGINE=InnoDB;

CREATE TABLE QualToTeach (
    ClassID VARCHAR(5),
    Emp_no INT,
    PRIMARY KEY (ClassID, Emp_no),
    FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
    FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)) ENGINE=InnoDB;


CREATE TABLE Enrolled (
    StudentID INT,
    ClassID VARCHAR(5),
    SecNo VARCHAR(10),
    Semester VARCHAR(4),
    Grade CHAR(2),
    DropDate DATE,
    PRIMARY KEY (StudentID, ClassID, SecNo, Semester),
    FOREIGN KEY (ClassID, SecNo, Semester) REFERENCES Section(ClassID, SecNo, Semester),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID))
    ENGINE=InnoDB;

Best Answer

I had some changes in that table and it got executed. mainly the primary key. pl go through the same,

    mysql> CREATE TABLE Student (
        ->     StudentID INT,
        ->     Fname VARCHAR(30),
        ->     Lname VARCHAR(30),
        ->     DOB DATE,
        ->     Major VARCHAR(10),
        ->     PRIMARY KEY (StudentID)) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.10 sec)

    mysql> CREATE TABLE Professor (
        ->     Emp_no INT,
        ->     FName VARCHAR(10),
        ->     LName VARCHAR(10),
        ->     DeptCode VARCHAR(2),
        ->     PRIMARY KEY (Emp_no)) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.14 sec)

    mysql> CREATE TABLE Phone (
        ->     StudentID INT,
        ->     PNumber VARCHAR(20),
        ->     Type VARCHAR(5),
        ->     FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE
        ->     ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE Class (
    ->     ClassID VARCHAR(5),
    ->     NoCredits INT,
    ->     Description VARCHAR(30),
    ->     Requisite1 VARCHAR(6),
    ->     Requisite2 VARCHAR(6),
    ->     Requisite3 VARCHAR(6),
    ->     PRIMARY KEY (ClassID)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)

            mysql> CREATE TABLE Section (
                ->     ClassID VARCHAR(5),
                ->     SecNo VARCHAR(10),
                ->     Semester VARCHAR(4),
                ->     ClassRoom VARCHAR(6),
                ->     TimeOffered VARCHAR(20),
                ->     Emp_no INT,
                -> PRIMARY KEY (SecNo, Semester),
                -> FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
                ->     FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)
                ->     ) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.13 sec)

            mysql> CREATE TABLE QualToTeach (
                -> ID varchar(5),
                -> ClassID VARCHAR(5),
                ->     Emp_no INT,
                ->     PRIMARY KEY (ID),
                -> FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
                ->     FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.13 sec)

            mysql> CREATE TABLE Enrolled (
                ->     StudentID INT,
                ->     ClassID VARCHAR(5),
                ->     SecNo VARCHAR(10),
                ->     Semester VARCHAR(4),
                ->     Grade CHAR(2),
                ->     DropDate DATE,
                -> Enrolled_id INT,
                -> PRIMARY KEY (Enrolled_id),
                -> FOREIGN KEY (ClassID, SecNo, Semester) REFERENCES Section(ClassID, SecNo, Semester),
                ->     FOREIGN KEY (StudentID) REFERENCES Student(StudentID))
                ->     ENGINE=InnoDB;
            Query OK, 0 rows affected (0.19 sec)
Related Question