如何在行删除时更改自动增量主键值? [英] How can I change auto increment primary key values on row deletion?

问题描述

我有一个问题，每当我删除一行时，与该行对应的行 ID 都会被删除，但我不想要这样.我想要的是，如果删除了任何一行，那么该行之后的其他行应该向上移动一个(删除的行数)位置.

I have a problem that whenever I delete a row, the row ID corresponding to that row gets deleted, but I don't want this. What I want is if any row is deleted, then other rows after that row should shift one (the no. of rows deleted) position up.

示例:

假设有一个用户表(id 和 name)

Suppose there is a user table(id and name)

id(auto incremented primary key)          name
1                                         xyz
2                                         aaa
3                                         ray
4                                         mark
5                                         allen

现在删除带有 id=3 的行，表格应该看起来像

now delete row with id=3 and table should look like

id(auto incremented primary key)          name
1                                         xyz
2                                         aaa
3                                         mark
4                                         allen

有没有办法做到这一点?

Is there any way to accomplish this?

推荐答案

不！不要这样做！

您的自动增量 ID 是一行的 IDENTITY.其他表使用此 ID 来引用特定行.如果您更新 ID，则必须更新所有其他引用该行的表，这完全不是关系数据库的点.

No! Don't do this!

Your Autoincrement ID is the IDENTITY of a row. Other tables use this ID to refer to a certain row. If you update the ID, you would have to update all other tables referencing this row, which is not at all the point of a relational database.

此外，从来没有必要这样做:您不会很快用完自动增量列(如果您这样做，只需选择更大的数据类型).

Furthermore, there never is a need to do this: you won't run out of autoincrement columns fast (and if you do, just pick a bigger datatype).

自动增量 ID 是一个纯粹的技术编号，您的应用程序用户永远不应看到或使用它.如果您想向用户显示标识符，请添加另一列！

An autoincrement ID is a purely technical number, your application users should never see or use it. If you want to display an identificator to your users, add another column!

这篇关于如何在行删除时更改自动增量主键值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！