SQL Script Foreign Key Error

Question

I am getting the error "Number of referencing columns in foreign key differs from number of referenced columns, table 'StudentGrade'" when trying to execute the following SQL script

CREATE TABLE StudentGrade

(

    StudentID INT NOT NULL
        CONSTRAINT FK_SG_StudentID FOREIGN KEY (StudentID)
        REFERENCES Student(StudentID),
    ClassID VARCHAR (6) NOT NULL
        CONSTRAINT FK_Class FOREIGN KEY (ClassID, CourseID)
        REFERENCES Class(ClassID),
    CourseID VARCHAR (64) NOT NULL
        CONSTRAINT FK_Course FOREIGN KEY (CourseID)
        REFERENCES Course(CourseID),
    FacultyID INT NOT NULL
        CONSTRAINT FK_Faculty FOREIGN KEY (FacultyID)
        REFERENCES Faculty(FacultyID),
    Grade NUMERIC NULL,
    CONSTRAINT PK_StudentID PRIMARY KEY (StudentID, ClassID, CourseID, FacultyID)

)

I know that there is something I am doing wrong with the foreign keys though I can't find anywhere where it explains how to use foreign keys and composite keys together. Any help would be greatly appreciated. Thank you so much!

Jose Rui Santos · Accepted Answer · 2011-06-07 06:40:26Z

1

change your second foreign key from

CONSTRAINT FK_Class FOREIGN KEY (ClassID, CourseID)

to

CONSTRAINT FK_Class FOREIGN KEY (ClassID)

In FK_Class you are referencing the columns StudentGrade.ClassID and StudentGrade.CourseID into a single one Class.ClassID and this cannot work.

Your FK_Course already refers CourseID, so you can simply delete CourseID from FK_Class as I said above.

Edit

Add CourseID to your definition of FK_Class as

CONSTRAINT FK_Class FOREIGN KEY (ClassID, CourseID)
        REFERENCES Class(ClassID, CourseID)

edited Jun 7, 2011 at 6:40

answered Jun 7, 2011 at 6:10

Jose Rui Santos

15.3k9 gold badges61 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zombian Over a year ago

Thank you for your response! Unfortunately that just give me another error There are no primary or candidate keys in the referenced table 'Class' that match the referencing column list in the foreign key 'FK_Class'. Maybe it would help if I showed my code for the Class table. CREATE TABLE Class ( ClassID VARCHAR (6) NOT NULL CONSTRAINT PK_ClassID PRIMARY KEY (ClassID, CourseID), CourseID VARCHAR (64) NOT NULL CONSTRAINT FK_CourseID FOREIGN KEY (CourseID) REFERENCES Course(CourseID), StartDate Date NULL, EndDate Date NULL, Location VARCHAR (64) NULL, )

Collectives™ on Stack Overflow

SQL Script Foreign Key Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related