Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

What to do when foreign key constraint is incorrectly formed?

Asked by Colin Payne Sep 29, 2022 4.9K views 1 answer
Share

About this question

 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?


Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.