Foreign Key error: MySQL ERROR 1005 (HY000): Can't create table

Question

I have a problem in which I get the error message:

MySQL ERROR 1005 (HY000): Can't create table

whenever I try to create a foreign key to link my two tables. I have tried a variety of different ways of phrasing the code but none seem to fix the error, the current code I am assuming is the closest to working:

ALTER TABLE requests
    ADD FOREIGN KEY FK_UserRequest(device_id) REFERENCES users(device_id)

I read through the other many posts regarding this topic but didn't seem to find a concise answer as to why this doesn't work. I'm hoping someone can specifically answer these questions:

• Does the foreign key need to reference a primary key? I've seen posts that say both yes and no to this question.
• Why does this specific code not work?

background info-

• engine is confirmed to be innoDB
• device_id is a column in both tables with the same attributes: CHAR(40)
• device_id in the users table is not a primary key
• database specifics: version 5.5.56-MariaDB

Answer 1

Yes, you need to ensure your referenced key is an index of some sort.

Taken from: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

MySQL requires indexes on foreign keys and referenced keys [...]

InnoDB permits a foreign key to reference any column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.