问题描述
我有一张桌子...
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键看起来很像一个数字,后跟一个日期时间,很可能是这样.例如,最有可能在此表的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
这篇关于在重复密钥更新时-获得“违反完整性约束"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!