Bug #16324 auto_increment primary key can be updated,and then insert record fail
Submitted: 10 Jan 2006 6:02 Modified: 28 Oct 2019 22:42
Reporter: ming lu Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.0.12 OS:Microsoft Windows (windows & linux)
Assigned to: CPU Architecture:Any

[10 Jan 2006 6:02] ming lu
create table t1 (a int not null auto_increment primary key, 
                       val int);
insert into t1 (val) values (1);
update t1 set a=2 where a=1;    //update the primary key

insert into t1 (val) values (1);    //'duplicate entry '2' for key1  '  errcode 1062  

after the primary key be updated, you cannot insert new record into this table anymore. 

Note: the engine i used is innodb

How to repeat:
see description

Suggested fix:
Could the auto_increment primary key be updated?  I think this is not reasonable.Why don't you forbid the update operation against to the auto_increment primary key?
[10 Jan 2006 7:21] Valeriy Kravchuk
Thank you for a problem report. I agree that it is reasonable to prevent (auto_increment) primary key update. At least one other RDBMS, IBM Informix, do prevent it.

Although, there is surely a lot of code that do change auto_increment columns, and for some valid reasons. So, I'll mark this as a verified feature request. Let developers check potential benefits and problems with its implementation.
[28 Oct 2019 22:42] Roy Lyseng
Posted by developer:
Implemented in 8.0