Why is my mySQL throwing “ERROR 1215 (HY000): Cannot add foreign key constraint”

Error Recieved:

ERROR 1215 (HY000): Cannot add foreign key constraint

Code:

CREATE TABLE servers (
    serverID varchar(20) NOT NULL,
    serverPrefix varchar(1) NOT NULL,
    captchaEnabled boolean NOT NULL,
    giveawayEnabled boolean NOT NULL,
    pollsEnabled boolean NOT NULL,
    reactRolesEnabled boolean NOT NULL,
    serverStatsEnabled boolean NOT NULL,
    storyTimeEnabled boolean NOT NULL,
    suggestionsEnabled boolean NOT NULL,
    welcomeEnabled boolean NOT NULL,
    captchaChannelID varchar(20),
    storyTimeChannelID varchar(20),
    suggestionsChannelID varchar(20),
    reportChannelID varchar(20),
    PRIMARY KEY (serverID)
);

CREATE TABLE welcomeMessage(
    welcomeMessageID int NOT NULL AUTO_INCREMENT,
    serverID varchar(20) NOT NULL, 
    `description` varchar(300), 
    instruction varchar(300), 
    footer varchar(100), 
    welcomeChannelID varchar(20), 
    DM boolean NOT NULL, 
    PRIMARY KEY (welcomeMessageID), 
    FOREIGN KEY (`serverID`) REFERENCES servers (`serverID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

​x
 
CREATE TABLE servers (    serverID varchar(20) NOT NULL,    serverPrefix varchar(1) NOT NULL,    captchaEnabled boolean NOT NULL,    giveawayEnabled boolean NOT NULL,    pollsEnabled boolean NOT NULL,    reactRolesEnabled boolean NOT NULL,    serverStatsEnabled boolean NOT NULL,    storyTimeEnabled boolean NOT NULL,    suggestionsEnabled boolean NOT NULL,    welcomeEnabled boolean NOT NULL,    captchaChannelID varchar(20),    storyTimeChannelID varchar(20),    suggestionsChannelID varchar(20),    reportChannelID varchar(20),    PRIMARY KEY (serverID));​CREATE TABLE welcomeMessage(    welcomeMessageID int NOT NULL AUTO_INCREMENT,    serverID varchar(20) NOT NULL,     `description` varchar(300),     instruction varchar(300),     footer varchar(100),     welcomeChannelID varchar(20),     DM boolean NOT NULL,     PRIMARY KEY (welcomeMessageID),     FOREIGN KEY (`serverID`) REFERENCES servers (`serverID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;​

The error is being thrown on “CREATE TABLE welcomeMessage”. I tried separating the create from the foreign key using:

ALTER TABLE welcomeMessage ADD FOREIGN KEY (`serverID`) REFERENCES servers(`serverID`);

 
ALTER TABLE welcomeMessage ADD FOREIGN KEY (`serverID`) REFERENCES servers(`serverID`);​

However it just creates the table then throws the error on the “Alter” line instead. Any help is appreciated.

Answer

The problem is the charset and the collation. For the foreign key to be well-formed, both the parent and the child columns must have the same charset and collation. The first statement relies on the default settings, while the second has explicit settings. If those are different from the defaults, a failure occurs.

Your code works if I just comment out the charset .... collate ... part of the second create table statement, as you can see in this db fiddle.

Both storage engines also need to be aligned. If your database uses MyISAM as a default engine, the code won’t work either (as the second statement explicitly uses InnoDB).

Advertisement

Answer