How to insert values to a table from 2 foreign keys

Question

Hey guys was wanting some help. I want to add the same value for Student_ID that is in the Studentinfo table in the GradeInfo and also wanted to add the same value for CourseNo that is in the CourseInfo table and add it to GradeInfo. Keep in mind that CourseNo in GradeInfo table is a foreign key from CourseInfo and Student_ID in GradeInfo is a foreign key from CourseInfo.

What I was wanting to know is how do I Insert these values from the foreign keys from other tables? I kept getting an error message of "foreign key mismatch - "GradeInfo" referencing "CourseInfo"

CREATE TABLE CourseInfo (           /*Created 2nd*/
    CourseNo Text,
    Year NUMERIC,
    Title TEXT,
    PRIMARY KEY (CourseNo, Year)
)



CREATE TABLE GradeInfo (                    /*Created 3rd*/
    Student_ID NUMERIC AUTO_INCREMENT,
    CourseNo Text,
    Pts NUMERIC,
    Grade TEXT,
    FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID)
    FOREIGN KEY (CourseNo) REFERENCES CourseInfo (CourseNo),
    PRIMARY KEY (Student_ID, CourseNo)
)

CREATE TABLE StudentInfo (            /*Created 1st*/
    Name TEXT,
    Student_ID NUMERIC AUTO_INCREMENT,
    Date_of_Birth NUMERIC,
    Gender TEXT,
    Address TEXT,
    Email TEXT,
    Degree TEXT,
    PRIMARY KEY (Student_ID)
)

This is my DML for the tables

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO151", "Databases", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)  
VALUES("ECON130", "Microeconomic Principles", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)  
VALUES("FCOM111", "Government, Law and Business", "2019")

NSERT INTO CourseInfo( CourseNo, Title, Year)   
VALUES("INFO101", "Foundations of Info Systems", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO141", "Systems Analysis", "2018")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO151", "Databases & SQL", "2018")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("MGMT101", "Introduction to Management", "2018"),

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("QUAN102", "Statistics for Business", "2018")

This is is already in my table as Data. It works fine.

INSERT INTO StudentInfo (Name, Student_ID, Date_of_Birth, Gender, Address, Email, Degree)
Values ("James bond", "007007", "07-07-1977", "Male", "10 Downing Street", "[email protected]", "Bachelor of Technology")

This is is already in my table as Data. It works fine.

INSERT INTO GradeInfo(Student_ID, CourseNo, Pts, Grade)
VALUES("7007", "INFO151", "15", "A+")

This is where I am having the problem. The values for student_ID and courseNo are already in the table but still get the error message of "foreign key mismatch - "GradeInfo" referencing "CourseInfo".

Note that Student_ID is a foreign key from StudentInfo and CourseNo is a foreign key from CourseInfo

Any help would be appreciated. Thank you:)

Answer 1

You have two issues:

1. A typo in your GradeInfo table definition that should be a syntax error, but unfortunately sqlite is all too often too lenient about what it accepts:

   FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID)
   FOREIGN KEY (CourseNo) REFERENCES CourseInfo (CourseNo),
   

   Notice the lack of comma. (EDIT: This actually works even though it shouldn't! Bug in Sqlite?)

2. The primary key on CourseInfo is (CourseNo, Year), but your foreign key only references CourseNo. You have multiple rows in CourseInfo with the same value for CourseNo. Which one is supposed to be used as the parent to the foreign key? Add tracking the year too to the table and it works:

     CREATE TABLE GradeInfo (                    /*Created 3rd*/
       Student_ID NUMERIC AUTO_INCREMENT,
       CourseNo Text,
       Year Numeric,
       Pts NUMERIC,
       Grade TEXT,
       FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID),
       FOREIGN KEY (CourseNo, Year) REFERENCES CourseInfo (CourseNo, Year),
       PRIMARY KEY (Student_ID, CourseNo, Year)
       );
   INSERT INTO GradeInfo(Student_ID, CourseNo, Year, Pts, Grade)
   VALUES(7007, 'INFO151', 2018, 15, 'A+');
   

Also, there's the double quote vs single quote issue that sticky bit mentioned already, and AUTO_INCREMENT is not a sqlite keyword - you should read this documentation about auto increment and how to use it (If you really need it; you usually don't; an INTEGER PRIMARY KEY is enough).