MySQL Error – Cannot add foreign key constraint

I know this question has been asked several times but it seemed like the problem was due to different data types between parent and child rows. In my case, the data types are the same but I’m still getting the error. Here’s my code

CREATE TABLE STUDENT_2(
    StudentNumber       INT                 NOT NULL AUTO_INCREMENT,
    StudentName         VARCHAR(50)         NULL,
    Dorm                VARCHAR(50)         NULL,
    RoomType            VARCHAR(50)         NOT NULL,
    CONSTRAINT          STUDENTPK           PRIMARY KEY(StudentNumber)
);

CREATE TABLE DORM_COST(
    RoomType            VARCHAR(50)         NOT NULL,
    DormCost            DECIMAL(7,2)        NULL,
    CONSTRAINT          DORM_COSTPK         PRIMARY KEY(RoomType),
    CONSTRAINT          DORM_COST_FK        FOREIGN KEY(RoomType)
        REFERENCES      STUDENT_2(RoomType)
                ON UPDATE CASCADE
                ON DELETE CASCADE
);

Where DORM_COSTS’ foreign key cannot be added. Thanks!

Answer

You want the foreign key reference on the table that has the foreign key, not the primary key. So, that would be:

CREATE TABLE DORM_COST (
    RoomType            VARCHAR(50)         NOT NULL,
    DormCost            DECIMAL(7,2)        NULL,
    CONSTRAINT          DORM_COSTPK         PRIMARY KEY(RoomType)
);

CREATE TABLE STUDENT_2(
    StudentNumber       INT                 NOT NULL AUTO_INCREMENT,
    StudentName         VARCHAR(50)         NULL,
    Dorm                VARCHAR(50)         NULL,
    RoomType            VARCHAR(50)         NOT NULL,
    CONSTRAINT          STUDENTPK           PRIMARY KEY(StudentNumber),
    CONSTRAINT fk_student2_roomtype FOREIGN KEY (RoomType) REFERENCES DORM_COST(RoomType)
);

Here is a db<>fiddle that shows that this works.

That said your data model seems quite strange.

• I would expect a table called RoomTypes to have a primary key of RoomTypeId.
• I would expect dorm_cost to have dates, because costs can vary from year to year.
• I would expect different dorms to have similar room types — singles, doubles, and so on.
• I would expect those room types to vary, perhaps, by dorm.