MySQL FOREIGN KEY constraint is incorrectly formed

2018-04-10 14:26:25

I have the following table definition:

CREATE TABLE `async_task` (

`idasync_task` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`idasync_type` int(10) unsigned NOT NULL,

`priority` tinyint(3) NOT NULL,

`status` enum('todo','doing','failed') NOT NULL DEFAULT 'todo',

`iduser` int(11) NOT NULL,

`date_added` datetime NOT NULL,

PRIMARY KEY (`idasync_task`),

KEY `priority_id` (`priority`,`idasync_task`),

KEY `status_type` (`status`,`idasync_type`)


I've added a column to the my notification table which I want to point to async_task:

ALTER TABLE `notification` ADD COLUMN `async_task_id` BIGINT(20)

And when I add the following foreign key:

ALTER TABLE `notification` ADD CONSTRAINT `fk_notification_async_task`

FOREIGN KEY (`async_task_id`) REFERENCES `async_task`(`idasync_task`);

I get:

ERROR 1005 (HY000): Can't create table `my_database`.`#sql-182_2d`

(errno: 150 "Foreign key constraint is inco