What to do when foreign key constraint is incorrectly formed?

4.0K    Asked by ColinPayne in SQL Server , Asked on Sep 29, 2022

 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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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 incorrectly formed")

I've looked elsewhere but only find the errors as being:

The table you're referencing is not created (not the case)

The table you're referencing is not InnoDB (not the case, both notification and async_task are InnoDB)

You're not referencing the entire primary key (not the case, the only primary key is the ID column).

What else could it be?


Answered by Connor Peake

When foreign key constraint is incorrectly formed -


Both the referencing and referenced columns must be of the same type (and same in this case includes the unsigned attribute).

You didn't define notification.async_task_id as unsigned, so it was created with the (default) signed. Fix that and the foreign key will raise no errors.



Your Answer

Interviews

Parent Categories