在重复密钥更新时-获得“违反完整性约束" [英] ON DUPLICATE KEY UPDATE - getting "Integrity constraint violation"

问题描述

我有一张桌子...

CREATE TABLE `pricing` (
    `product_id` int(11) NOT NULL,
    `seller_id` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `price` float DEFAULT NULL,
    `ship` float DEFAULT NULL,
    PRIMARY KEY (`product_id`),
    KEY `seller_id` (`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

当我尝试运行INSERT INTO ... ON DUPLICATE KEY UPDATE时，如果尝试编辑seller_id，则会遇到完整性约束违规的情况:

And when I try to run an INSERT INTO ... ON DUPLICATE KEY UPDATE, I get an integrity constraint violation, if I try to edit seller_id:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4984-2014-11-23 10:05:20' for key 'PRIMARY'.

SQL查询为:

INSERT INTO pricing
       (product_id, price, ship, seller_id) 
VALUES (:product_id, :price, :ship, :seller_id) 
ON DUPLICATE KEY UPDATE 
    seller_id = :seller_id

这是理想的行为吗?有办法避免吗?我们现在正在使用REPLACE，但是我对自己是否做错了很好奇.我不是在更新主键，而是另一个常规的非唯一键-并且约束落在主键上.

Is that desired behavior? Is there a way to avoid it? We're using REPLACE for now, but I'm curious as to whether I am doing something wrong or not. I'm not updating the primary key mind you, but another regular non-unique key - and the constraint falls on the primary key.

这里正在发生什么过程?

What is the process that's going on here?

编辑:我运行了 SQL提琴，它工作正常.为什么会在我的机器上引起错误? :/

I ran an SQL Fiddle and it works okay. Why would it cause errors on my machine? :/

推荐答案

您在该表中列出的查询对我来说很好，因此错误不在其中.

The query you listed with that table works fine for me, so the error is not in there.

仔细检查错误后，我注意到以下内容:

Upon taking a closer look at the error however I notice the following:

键"PRIMARY"的条目"4984-2014-11-23 10:05:20"重复.

Duplicate entry '4984-2014-11-23 10:05:20' for key 'PRIMARY'.

此PRIMARY键看起来很像一个数字，后跟一个日期时间，很可能是这样.例如，最有可能在此表的mysql数据库上有一个触发器.

This PRIMARY key looks a lot like a number followed by a datetime, which it most likely is. Most likely there is for example a trigger on the mysql database for this table.

(基于id +日期，我可能会说一些可以自动跟踪价格变化的信息?)

(Based on the id+date I'd say probally something to keep track of price changes automatically?)

另一种选择是可能会出现外键问题，尽管这是基于不太可能发生的错误.

Another option is the possibility that there is a foreign key issue, though based on the error that is not likely.

尝试在表的数据库中运行以下mysql语句，并查看结果.

Try running the following mysql statement in the database of the table and take a look at the result.

SHOW TRIGGERS 

这篇关于在重复密钥更新时-获得“违反完整性约束"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！