Restricting input from two other tables
this runs in MySQL as in Oracle 18c
So you must have somewhere a typo in your code


CREATE TABLE Department (
    Name varchar(255) NOT NULL PRIMARY KEY
);




✓




CREATE TABLE Person (
    SSN CHAR(9) NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);


✓




CREATE TABLE Professor (
    SSN CHAR(9),
    PRIMARY KEY(SSN),
    FOREIGN KEY (SSN) REFERENCES Person(SSN)
);


✓




CREATE TABLE WorksFOR (
    SSN CHAR(9),
    Salary float NOT NULL,
    DepName varchar(255),
    CONSTRAINT WorksFor_Salary CHECK (Salary&gt;=0),
    CONSTRAINT WorksFor_pk PRIMARY KEY(SSN,DepName),
    CONSTRAINT WorksFor_ssn_fk FOREIGN KEY(SSN) REFERENCES Professor(SSN),
    CONSTRAINT WorksFor_dep_fk FOREIGN KEY(DepName) REFERENCES Department(Name)
);


✓




CREATE TABLE Course (
    CourseDepartment varchar(255) NOT NULL,
    CourseNumber varchar(8) NOT NULL,
    CONSTRAINT Course_pk PRIMARY KEY(CourseDepartment,CourseNumber),
    FOREIGN KEY(CourseDepartment) REFERENCES Department(Name)
);


✓




CREATE TABLE CanTeach (
    SSN CHAR(9) NOT NULL,
    Department varchar(255) NOT NULL,
    CourseNum varchar(8) NOT NULL,
    CONSTRAINT CanTeach_pk PRIMARY KEY(SSN,Department,CourseNum),
    FOREIGN KEY(SSN,Department) REFERENCES WorksFOR(SSN,DepName),
    FOREIGN KEY(Department,CourseNum) REFERENCES Course(CourseDepartment,CourseNumber)
);


✓


db&lt;&gt;fiddle here