0

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;
2
  • Please run Show Engine InnoDB status it will shows you the latest Foreign key error and share the result.
    – Karthick
    Jul 30, 2014 at 5:09
  • The problem is this FK: FOREIGN KEY (ClassID, SecNo, Semester) REFERENCES Section(ClassID, SecNo, Semester), because Section(ClassID, SecNo, Semester) is not the PK (or a Unique key) of Section. Jul 30, 2014 at 8:13

1 Answer 1

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.