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?

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.

I'm trying to create a database through phpMyAdmin by importing a SQL file. The users table is created but the writing table has an error: Foreign Key Constraint is incorrectly formed. I read the MySQL 8.0 reference manual but I can't seen to find the error. Please tell me what I'm doing wrong.

Here are some handy troubleshooting tips to help you resolve MySQL foreign key constraint is incorrectly formed error. Our MySQL Support team is here to lend a hand with your queries and issues.

MySQL foreign key constraint is incorrectly formed | Resolved

If you have been running into this error, you are in the right place. Our experts have put together several troubleshooting tips to help you resolve this error in a jiffy:

• First, verify the foreign key column and the referenced column are the same data type.
• Check if the reference column is set as primary_key. If not, then ensure it is INDEXED. Referenced columns must be indexed in both the parent and child tables.
• Referenced columns must be indexed in both the parent and child tables.
• Next, check if there are duplicate values in referenced columns.
• The maximum length of the referenced columns has to be equal to or less than the foreign key’s maximum length.
• If any referenced column or table names are reserved words in MySQL, we have to surround them with backticks (`) in the SQL statement.
• Finally, verify the syntax of the foreign key definition.

Let us know in the comments which one of the above troubleshooting tips helped you resolve the MySQL foreign key constraint is incorrectly formed error.

[Need assistance with a different issue? Worry not, our team is available 24/7.]

Conclusion

To sum up, our Support Engineers demonstrated how to resolve the “foreign key constraint is incorrectly formed” error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Add heading text Add bold text, Add italic text,

Add a quote, Add code,

Insert Link

Link Text

URL

Add

Add a link,

Add a bulleted list, Add a numbered list, Add a task list,

Directly mention a user or team

Reference an issue or pull request

Add heading text Add bold text, Add italic text, Add a bulleted list, Add a numbered list, Add a task list,