I'm working on a SQL assignment using mySQL. I have to add some sort of constraint to a foreign key.

it is that if CourseOffeing does have a value for instructor, it MUST match an instructor in the instructor table.

use University;

/* create table CourseOfferings ( id int AUTO_INCREMENT, year int NOT NULL, semester varchar(15) NOT NULL, section varchar(3), instructor varchar(50), -- no value required, if present must match instructor id course_id int, FOREIGN KEY(course_id) REFERENCES Courses(id), PRIMARY KEY(id) );

create table instructor ( id int AUTO_INCREMENT, fname varchar(100), lname varchar(100), dept varchar(100), -- NOT REQUIRED TO HAVE VALUE PRIMARY KEY(id) ); */

-- NOTE: make instructor column instructor_ID column in courseOfferings -- ALTER TABLE courseOfferings RENAME COLUMN instructor TO instructor_id;

-- NOTE: had to clear the string values in instructor ID before i could change its type to int -- UPDATE courseOfferings SET instructor_id = NULL WHERE instructor_id IS NOT NULL; -- ALTER TABLE courseOfferings MODIFY instructor_id int; -- LTER TABLE courseOfferings ADD CONSTRAINT FOREIGN KEY(instructor_id) REFERENCES instructor(id); -- SELECT * from courseOfferings; -- validating functionality of courseOfferings table

-- NOTE: inserting some sample values into INSTRUCTOR MAKING SURE IT WORKED -- INSERT INTO instructor (fname, lname, dept) VALUES ("Jean", "French", "CSCI"); -- INSERT INTO instructor (fname, lname, dept) VALUES ("A.", "Lovelace", "CSCI"); -- INSERT INTO instructor (fname, lname, dept) VALUES ("Bill", "Gates", "CSCI"); -- INSERT INTO instructor (fname, lname, dept) VALUES ("Jeff", "Bezos", "CSCI"); -- INSERT INTO instructor (fname, lname, dept) VALUES ("M.", "Murphy", "MATH"); -- INSERT INTO instructor (fname, lname, dept) VALUES ("Steve", "Wozniak", "BIO");

-- NOTE: Verifying values were properly inserted -- SELECT * FROM instructor;

-- NOTE: Updating courseofferings instructor_ID column to have proper foreign key values and Verifying -- UPDATE courseOfferings SET instructor_id = 1 WHERE id = 1; -- UPDATE courseOfferings SET instructor_id = 1 WHERE id = 2; -- UPDATE courseOfferings SET instructor_id = 2 WHERE id = 3; -- UPDATE courseOfferings SET instructor_id = 3 WHERE id = 4; -- UPDATE courseOfferings SET instructor_id = 4 WHERE id = 5; -- UPDATE courseOfferings SET instructor_id = 5 WHERE id = 6; -- UPDATE courseOfferings SET instructor_id = 5 WHERE id = 7; -- UPDATE courseOfferings SET instructor_id = 6 WHERE id = 8; -- UPDATE courseOfferings SET instructor_id = 1 WHERE id = 9; -- SELECT * FROM courseOfferings;