MySQL中密钥“PRIMARY”的重复条目

Query for creating table :

查询创建表:

create table if not exists person ( 
roll_no int(4) AUTO_INCREMENT primary key,
name varchar(25),  
city varchar(25));

Query to set start number for auto-increment primary key :

查询以设置自动增量主键的起始编号:

alter table person auto_increment = 1;

alter table person auto_increment = 1;

Query to insert data :

查询插入数据:

insert into person (name,city) values("Maxwell", "Pune");
insert into person (name,city) values("Baldwin", "Bengaluru");
insert into person (name,city) values("Novartis", "Paris");
insert into person (name,city) values("Shaun", "Mumbai");
insert into person (name,city) values("Beckham", "Toronto");
insert into person (name,city) values("Ashish", "Bengaluru");
insert into person (name,city) values("David", "Paris");
insert into person (name,city) values("PK", "London");
insert into person (name,city) values("Chris", "Bengaluru");
insert into person (name,city) values("Aston", "Mumbai");

Query to delete the row :

查询删除行:

delete from person where roll_no=5;

从roll_no = 5的人中删除;

Table structure after deleting the row:

删除行后的表结构:

roll_no      name       city
1            Maxwell    Pune
2            Baldwin    Bengaluru
3            Novartis   Paris
4            Shaun      Mumbai
6            Ashish     Bengaluru
7            David      Paris
8            PK         London
9            Chris      Bengaluru
10           Aston      Mumbai

Now, while looking to reinstate the deleted row, the compiler is throwing error as " Duplicate entry '5' for key 'PRIMARY' "

现在,在寻找恢复已删除的行时,编译器将错误称为“重复条目'5',用于键'PRIMARY'”

Query used to re-instate the deleted row.

用于重新启动已删除行的查询。

update person set roll_no = roll_no + 1 where roll_no >=4 order by roll_no desc;
insert into person (roll_no, name, city) VALUES (5, "Beckham", "Toronto");

What could be the possible reason for this issue ? Any inputs would be highly recommended.

这个问题的可能原因是什么?强烈建议任何输入。

2 个解决方案

#1

To restore the deleted row, it is only necessary to perform the insert statement. Can simply remove the update statement and use following :

要恢复已删除的行,只需执行insert语句。可以简单地删除更新语句并使用以下内容:

query

insert into person (roll_no, name, city) VALUES (5, "Beckham", "Toronto");

#2

So simple, in your update query you are making 4 become 5 and then trying to insert 5. That's why its a duplicate.

如此简单,在您的更新查询中,您将4变为5,然后尝试插入5.这就是为什么它重复。

#1