Restricting input from two other tables I'm trying to create the table CanTeach but I am getting an error ERROR 1005 (HY000): Can't create table 'mydb'.'CanTeach' (errno: 150 &quot;Foreign key constraint is incorrectly formed&quot;). Ultimately, I'm trying to create a table of &quot;classes that teacher's can teach&quot; verifying they work for the department that offers the class.
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)
);
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