MySQL FOREIGN KEY constraint is incorrectly formed

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:

1. The table you're referencing is not created (not the case)
2. The table you're referencing is not InnoDB (not the case, both notification and async_task are InnoDB)
3. You're not referencing the entire primary key (not the case, the only primary key is the ID column).

What else could it be?

Answer 1

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.

Answer 2

also the character set of the two tables may be different. Do a "SHOW CREATE TABLE table1" and "SHOW CREATE TABLE table2" and pay attention to the ending like DEFAULT CHARSET=utf8 or DEFAULT CHARSET=utf8mb4, those are not the same and can't have FKs pointing between them.

Answer 3

I had the same issue, my problem was because the table I was trying to reference had not been created yet. If the table you're trying to reference isn't created it will come back with this error. Try creating the reference table first and then try again.

Answer 4

In my case, the foreign column had to be a primary key.

Table A

$table = $schema->createTable('items');
$table->addColumn('name', Types::STRING, ['notnull' => false]);
$table->setPrimaryKey(['name']); // <-- this was needed

Table B

$table = $schema->createTable('items_tags');
$table->addColumn('name', Types::STRING, ['notnull' => false]);
$table->addForeignKeyConstraint('items', ['name'], ['name']);

Answer 5

I had the same problem, I found the error. I was done that way:

ALTER TABLE budgets
     ADD CONSTRAINT fk_client FOREIGN KEY id_client REFERENCES clients id_client;

But it's necessary to use `` at columns, I altered the table that way:

 ALTER TABLE `budgets` ADD CONSTRAINT fk_client FOREIGN KEY (`id_client`) REFERENCES clients (`id_client`);

Answer 6

In this case, I used XAMPP Control Panel.

I created my database that way:

after that, I did commands SQL, and I added my tables that way:

CREATE TABLE `projetointegrador`.`produtos`(
`id_product` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(60) NOT NULL,
`value_product` FLOAT NOT NULL,
PRIMARY KEY(`id_product`)) ENGINE = InnoDB;
CREATE TABLE projetointegrador.clientes(
id_client INT NOT NULL AUTO_INCREMENT,
name VARCHAR(60) NOT NULL,
phone INT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
cpf INT(14) NOT NULL,
endereco VARCHAR(100) NULL,
PRIMARY KEY(id_client)) ENGINE = InnoDB;
CREATE TABLE projetointegrador.orcamentos(
id_orcamento INT NOT NULL AUTO_INCREMENT,
id_client INT NOT NULL,
id_product INT NOT NULL,
start_date DATETIME NOT NULL,
due_date DATETIME NOT NULL,
value_orcamento FLOAT NOT NULL,
PRIMARY KEY(id_orcamento)) ENGINE = InnoDB;

I added the Foreign Keys that way:

ALTER TABLE `orcamentos`
ADD CONSTRAINT fk_client FOREIGN KEY (`id_client`) REFERENCES clientes (`id_client`);
ALTER TABLE orcamentos
ADD CONSTRAINT fk_product FOREIGN KEY (id_product) REFERENCES produtos (id_product);