CODEWITHSUNDEEP
mysqlforeign-keysduplicatesconstraints
Expedito
Expedito

Reputation: 7805

ON DUPLICATE KEY UPDATE causing foreign key constraint error

I searched the StackOverflow, but I couldn't find an answer to my specific question.

Recently I have become a big fan of updating records with a list, using "INSERT INTO...ON DUPLICATE KEY UPDATE." However, I had problems with the following query:

INSERT INTO itens (prod_id, qtd) VALUES (410, 1),(442, -1)
ON DUPLICATE KEY UPDATE qtd = qtd + VALUES(qtd)

That resulted in the error: "Cannot add or update a child row: a foreign key constraint fails."

Here is the structure of the table in question. "carrinho_id" is a foreign key. I was puzzled by the fact that I wasn't trying to do anything with the foreign key. I simply wanted to update the quantity.

CREATE TABLE IF NOT EXISTS `itens` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `carrinho_id` int(10) unsigned NOT NULL DEFAULT '1',
 `prod_id` int(10) unsigned NOT NULL DEFAULT '1',
 `qtd` int(12) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `carrinho_id` (`carrinho_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=229 ;

Upvotes: 3

Views: 4413

Answers (1)

Expedito
Expedito

Reputation: 7805

I found the answer to my own problem and thought it might help others in a similar situation.

The problem is that trying to update using an insert, you have to think in terms of all the constraints of inserting. Any values that are undefined have to have defaults. I had a default for 'carrinho_id', but it was for a record that was already deleted, and for that reason I got the error.

It turned out that that wasn't my only problem. In order for MySql to detect a duplicate, you have to specify a row that can only be unique. Since I left out 'id' it simply added a new row, subracting the value for quantity from zero. It's also important to note that you can subtract quantities in that way only if the column is unsigned.

In the end, I succeeded by specifying both the primary key and the foreign key:

INSERT INTO itens (id, carrinho_id, prod_id, qtd) VALUES (225, 75, 410, 1),(226, 75, 442, -1)
ON DUPLICATE KEY UPDATE qtd = qtd + VALUES(qtd)

Upvotes: 7

Related Questions