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

Viewed 5202

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.

1 Answers
Related