MySQL Error #1215 - Cannot Add Foreign Key Constraint

Question

I have the following problem with a pretty simple MySQL database table creation:

SQL query:

CREATE TABLE IF NOT EXISTS comments(  
    commentId INT NOT NULL AUTO_INCREMENT,  
    comment VARCHAR(1024) NOT NULL,  
    commentdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    profileId INT NOT NULL,  
    PRIMARY KEY(commentId),  
    CONSTRAINT fk_ProfilesProfileId FOREIGN KEY(profileId) REFERENCES  profiles.profileId ON UPDATE RESTRICT ON DELETE CASCADE  
)Engine=InnoDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci  

Error:

MySQL said: Documentation #1215 - Cannot add foreign key constraint

As you can see, the error number is 1215, which would indicate that the problem is about Foreign Key and Primary Key in comments and profiles tables (respectively) being different types. However, I have checked multiple times that they both are INT(11) and signed.

Here are queries for both of them:
'profiles' -table

CREATE TABLE IF NOT EXISTS profiles(
    profileId INT NOT NULL AUTO_INCREMENT,
    corpname VARCHAR(512) NOT NULL DEFAULT 'VIRHE',
    corpserial VARCHAR(16) NOT NULL DEFAULT 'VIRHE',
    loginusername VARCHAR(128) NOT NULL,
    loginpassword VARCHAR(128) NOT NULL,
    profilephone VARCHAR(16),
    mapsphone VARCHAR(16),
    added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    done TIMESTAMP DEFAULT NULL,
    coderequest TIMESTAMP DEFAULT NULL,
    confirmed TIMESTAMP DEFAULT NULL,
    PRIMARY KEY(profileId),
    INDEX corpnameIndex (corpname),
    INDEX corpserialIndex (corpserial)
)Engine=InnoDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

'comments' -table

CREATE TABLE IF NOT EXISTS comments(
    commentId INT NOT NULL AUTO_INCREMENT,
    comment VARCHAR(1024) NOT NULL,
    commentdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    profileId INT NOT NULL,
    PRIMARY KEY(commentId),
    CONSTRAINT fk_ProfilesProfileId FOREIGN KEY(profileId) REFERENCES profiles.profileId ON UPDATE RESTRICT ON DELETE CASCADE
)Engine=InnoDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

It is possible that I am dumb, and I'm just missing something. Hopefully it's so.

Answer 1

I replicated your example locally. It seems that the problem is how you declare your constraint for the foreign key.

CREATE TABLE profiles (
  profileId int(11) NOT NULL AUTO_INCREMENT,
  corpname varchar(512) NOT NULL DEFAULT 'VIRHE',
  corpserial varchar(16) NOT NULL DEFAULT 'VIRHE',
  loginusername varchar(128) NOT NULL,
  loginpassword varchar(128) NOT NULL,
  profilephone varchar(16) DEFAULT NULL,
  mapsphone varchar(16) DEFAULT NULL,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  done timestamp NULL DEFAULT NULL,
  coderequest timestamp NULL DEFAULT NULL,
  confirmed timestamp NULL DEFAULT NULL,
  PRIMARY KEY (profileId),
  KEY corpnameIndex (corpname(255)),
  KEY corpserialIndex (corpserial)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS comments(
    commentId INT NOT NULL AUTO_INCREMENT,
    comment VARCHAR(1024) NOT NULL,
    commentdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    profileId INT NOT NULL,
    PRIMARY KEY(commentId),
    CONSTRAINT fk_ProfilesProfileId FOREIGN KEY(profileId) REFERENCES profiles(profileId) ON UPDATE RESTRICT ON DELETE CASCADE
)Engine=InnoDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

with respect to your code:

1. The syntax for the foreign key after REFERENCES is table(field) rather than table.field
2. TIMESTAMP DEFAULT NULL should be TIMESTAMP NULL.
3. Note the partial index corpnameIndex, some considerations follow hereunder.

Some information about indexes the engine you want to use is InnoDB, which specifies that (from here):

By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

The problem in your table is INDEX corpnameIndex (corpname), because this index would be 512*3 = 1536 bytes long.

You have some options here:

1. Change corpname size to 255 by declaring it as follows corpname VARCHAR(255) NOT NULL DEFAULT 'VIRHE'

2. Enable innodb_large_prefix on your mysql by running set global innodb_large_prefix = ON;. This will bring the limit to 3072 bytes (1024 chars).

3. Index only a part of your corpname field by using ADD INDEX (corpname(255))