Why UNSIGNED can't be used for FOREIGN KEY in MYSQL?

Question

I am facing a strange issue when I want to create a table with the foreign key.

Please see the following result:

mysql> CREATE TABLE IF NOT EXISTS TL_USER(
    ->   user_id INT(10) UNSIGNED NOT NULL PRIMARY KEY,
    ->   nickname VARCHAR(45) NULL
    ->   ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

---

mysql> CREATE TABLE IF NOT EXISTS TL_EVENT_COMMENT (
    ->   event_comment_id INT PRIMARY key ,
    ->   comment VARCHAR(45) NULL,
    ->   user_id INT NULL,
    ->   FOREIGN KEY (`user_id`) REFERENCES TL_USER (`user_id`)
    ->   ) ENGINE = InnoDB;

ERROR 1005 (HY000): Can't create table 'TL_EVENT_COMMENT' (errno: 150)

when I change "user_id INT(10) UNSIGNED NOT NULL PRIMARY KEY" to "user_id INT(10) NOT NULL PRIMARY KEY", it works.

mysql> CREATE TABLE IF NOT EXISTS TL_USER(
    ->   user_id INT(10) NOT NULL PRIMARY KEY,
    ->   nickname VARCHAR(45) NULL
    ->   ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS TL_EVENT_COMMENT (
    ->   event_comment_id INT PRIMARY key ,
    ->   comment VARCHAR(45) NULL,
    ->   user_id INT NULL,
    ->   FOREIGN KEY (`user_id`) REFERENCES TL_USER (`user_id`)
    ->   ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

The problem is when I add UNSIGNED for user_id, I get that error if I don't add it, it works, could anyone explain why UNSIGNED can't be used for FOREIGN KEY?

How can I make it work?

MYSQL version is 5.5.47-0ubuntu0.14.04.1.

Answer 1

Referenced, and referee must be of same definition. If you add unsigned to main, then foreign key must also be unsigned, and vice versa. Type and definition of fields must match.

I know sometimes it is a pain, that's why you need to be consistent, especially when dealing with many tables. For instance make a rule for yourself to assign all primary keys to be UNSIGNED INT(11), this way you don't have to guess which definition to use for a foreign key.