22

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?

0

6 Answers 6

22

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.

0
6

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.

0
2

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.

0
1

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']);
0
-2

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`);
2
  • The real difference between the 1st (that doesn't work) and the 2nd (that works) is the parentheses. The backticks are only needed when there are spaces or other non alphanumeric characters in table/column/constraint names or they are reserved keywords. Nov 3, 2022 at 7:36
  • @ypercubeᵀᴹ thank you very much for the answer here, I didn't know about it. I used XAMPP Control Panel in this case. Nov 4, 2022 at 11:42
-3

In this case, I used XAMPP Control Panel.

I created my database that way:

enter image description here

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`);
2
  • 2
    That's interesting but how does it address the question asked? Nov 3, 2022 at 6:01
  • I wanted to show another way, In this case, I used XAMPP Control Panel Nov 4, 2022 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.