Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint

create_user INT UNSIGNED ZEROFILL cannot reference id INT, because these count as different data types for purposes of foreign key reference. Make them the same data type.

The only data type difference that is permitted between columns in a foreign key relationship is length of a varchar. For example, VARCHAR(10) can reference VARCHAR(20) or vice-versa.

Any other difference in data type, size, or character set is incompatible for referential integrity.

Even having ZEROFILL on one column but not the other makes the data types incompatible.

I came across this issue and my Data Type was correct so I was stumped for a little but then I just made everything the same.

When creating foreign keys be sure the columns you are using have the same:

• Data Type
• Collation
• Zero Fill
• Not Null
• Unsigned
• Binary

You could use SHOW FULL COLUMNS FROM table_name which returns a column Collation, for example for a table accounts with a special collation on the column name

mysql> SHOW FULL COLUMNS FROM accounts;
+----------+--------------+-------------------+------+-----+---------+----------+
| Field    | Type         | Collation         | Null | Key | Default | Extra    |
+----------+--------------+-------------------+------+-----+---------+----------|
| id       | int(11)      | NULL              | NO   | PRI | NULL    | auto_inc |
| name     | varchar(255) | utf8_bin          | YES  |     | NULL    |          |
| email    | varchar(255) | latin1_swedish_ci | YES  |     | NULL    |          |
...

Both columns have to has the same collation.

To change the collation of column

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;

It's happened to me.