Strange foreign key error with MySQL under MariaDB

Question

For some context, I am creating a javascript bot for discord and I'm creating a large database so that I can log many different pieces of information.

This part of the database is used so that I can

store messages
store historical messages, with every edit
easily look up which users are mentioned within these historical messages.

I am using MariaDB with MySQL Workbench under arch linux. Here's the relevant part of the database design, done on dbdesigner.net. The problem happens at the left-most table.

And here's my error message:

Finished executing script ERROR 1005 (HY000) at line 158: Can't create table test.#sql-1bb_6 (errno: 150 "Foreign key constraint is incorrectly formed") Operation failed with exitcode 1

So I know that there's a problem with my foreign key at like 158 but I can't find the problem.

ALTER TABLE `messageContentHistoryMentions` 
    ADD CONSTRAINT `messageContentHistoryMentions_fk1` 
        FOREIGN KEY (`messageContentCount`) REFERENCES `messageContentHistory`(`messageContentCount`);

The part that puzzles me about this is that 158 is formatted in the exact same way as line 156, only with different fields. Line 156 below runs:

ALTER TABLE `messageContentHistoryMentions` 
    ADD CONSTRAINT `messageContentHistoryMentions_fk0` 
        FOREIGN KEY (`messageID`) REFERENCES `messageContentHistory`(`messageID`);

You can probably understand why I am very confused.
Some more information:

This is a new install of MariaDB so assume defaults
InnoDB is the engine being used
The tables all have a collation of utf8mb4_unicode_ci
Both sides of the foreign key are primary keys, are integer and have a length of 8, default of 0.

Thanks.
edit: this is not a duplicate as while the error is similar, the situation it is in is very different

150 often means that the constraints were applied in the wrong order. — Rick James
See mysql Foreign key constraint is incorrectly formed error — joanolo
Possible duplicate of mysql Foreign key constraint is incorrectly formed error — joanolo
It's likely because messageID is already an index, such as a primary key, and the other field is not. So, make sure that messageContentCount is indexed for the messageContentHistory table. — Paul T.

elenst elenst · Accepted Answer · 2017-07-15T23:46:12

The referenced table, in your case messageContentHistory, must have an index where the referenced columns, in your case messageContentCount, are listed as the first columns (in the given order). According to the schema, messageContentCount is a part of the primary key, but not the first part. messageID is the leftmost part of the primary key, that's why it works for it.

Try to add another index on messageContentCount:

ALTER TABLE messageContentHistory ADD KEY (messageContentCount);

It should work after that.

Alternatively, if it suits your purposes, you can add one foreign key on (messageID,messageContentCount) instead of the two separate ones. It should work too, because in this case the condition will be met.

MariaDB KB for foreign keys (the interesting part is The columns in the child table must be an index...)

Strange foreign key error with MySQL under MariaDB

1 Answers