我想重用已删除行的空白 [英] I want to reuse the gaps of the deleted rows

问题描述

我在一张桌子上有一个自动递增的主键.如果我有3行，例如，删除第三行，则剩下两行.但是，如果我插入新行，其ID自动为4，ID为1、2和4.

I have a auto-increment primary key on one of my tables. If I have 3 rows and, for example, delete the third row I'm left with two. However, if I insert a new row its ID is automatically 4 and the IDs are 1, 2 and 4.

如何重新使用已删除的ID，并使新插入的记录的ID自动为3?

How can I re-use the deleted ID and have the ID of the newly inserted record to be 3 automatically?

推荐答案

确实，您不应该这样做.主键应纯粹是技术性的，无意义的值.它们的价值和这一代的单调根本不重要.

Really, you shouldn't. Primary keys should be purely technical, meaningless values. Their value, and the monotony of the generation, shouldn't matter at all.

此外，由于它是该行的PK，因此您在其他表中可能还会有数十个(或数千个)其他行引用该ID(外键)，因此在表中对其进行更改是不够的:您将必须到处更改它.

Moreover, since it's the PK of the row, you'll have potentially dozens (or thousands) of other rows in other tables referencing this ID (foreign keys), so changing it in the table would not be enough: you would have to change it everywhere.

很有可能在其他应用程序中也引用了此ID(例如，它可能是浏览器中带有书签的URL的一部分)，并且更改其值会使所有这些引用无效.

And there's a good chance that this ID is also referenced in other applications (for example, it could be part of a bookmarked URL in a browser), and changing its value would make all these references invalid.

您永远不要更改主键.它应该是永远不变的.

You should never change a primary key. It should be immutable, forever.

我误解了问题.您实际上想重用旧的ID.这也是一个坏主意.现有参考文献将引用最初引用的内容以外的内容.当您更改电话号码并被其他人重新使用时，就会发生这种情况，其他人开始收到仍然认为该电话号码是您的电话的人打来的电话.很烦人.您想避免这种情况.

I misread the question. You actually want to reuse an old ID. This is also a bad idea. Existing references would reference something other than they initially referenced. This is what happens when you change your phone number and it's being reused by someone else, who starts receiving lots of calls from people who still think this phone number is yours. Very annoying. You want to avoid this situation.

这篇关于我想重用已删除行的空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！