0

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

  1. store messages
  2. store historical messages, with every edit
  3. 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

Sobeston
  • 3
  • 5
  • 150 often means that the constraints were applied in the wrong order. – Rick James Jul 15 '17 at 22:45
  • See [mysql Foreign key constraint is incorrectly formed error](https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error) – joanolo Jul 15 '17 at 22:51
  • Possible duplicate of [mysql Foreign key constraint is incorrectly formed error](https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error) – joanolo Jul 15 '17 at 22:52
  • 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. Jul 15 '17 at 22:54
  • @PaulT. Both fields are primary keys – Sobeston Jul 15 '17 at 23:10
  • @Sobeston: ...right, but it was likely not indexed. I see that **elenst** already answered, so no big deal. I basically said that the same thing to the first part of the answer, but he added the additional alternative to do both. – Paul T. Jul 16 '17 at 01:08

1 Answers1

0

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...)

elenst
  • 3,839
  • 1
  • 15
  • 22
  • Thank you so much! I used your suggestion of doing (messageID,messageContentCount). This fixed the problem at line 158, and later on at line 164, where the same error occurred. – Sobeston Jul 16 '17 at 00:42