Bug #68360 | Duplicate entry for key while updating | ||
---|---|---|---|
Submitted: | 13 Feb 2013 10:36 | Modified: | 15 Feb 2013 18:00 |
Reporter: | Andreas Mller | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.5.28, 5.0.97, 5.1.69, 5.5.31, 5.7.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | key violation update |
[13 Feb 2013 10:36]
Andreas Mller
[13 Feb 2013 10:44]
Hartmut Holzgraefe
Workaround: ... ORDER BY sort_order DESC MySQL does not defer any constraint checks until commit, everything is checked immediately, and for unique keys i don't even thing deferring the check makes sense ... is there any other DB product out there that works this way? For foreign key constraints the limitation is documented on http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html " In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. "
[13 Feb 2013 10:55]
Andreas Mller
As described it works i.e. with ms-sql or oracle. The workaround works for me but I think it is a violation of the sql idea. A single statement should work if the result is correct.
[15 Feb 2013 18:00]
Sveta Smirnova
Thank you for the reasonable feature request. Test case: create table t1(f1 int unique, f2 int); insert into t1 values (1,1), (2,1), (3,1); select * from t1; update t1 set f1 = f1 + 1 where f2 = 1; select * from t1; PostgreSQL works like MySQL: test=# update t1 set f1 = f1 + 1 where f2 = 1; ERROR: duplicate key value violates unique constraint "t1_f1_key"