如何在整个删除操作中以连续增长的顺序保持表字段值? [英] how to keep a table field value in consecutive growing order throughout delete operations?

问题描述

我正在处理数据库中的一个表，该表具有一个带整数值的主键字段.

I'm working on a table in a database which has a primary key field with numeric int value.

CREATE TABLE 'table' (
    'primary_key_field' INT NOT NULL
    'other_fields' ....
    PRIMARY KEY ( 'primary_key_field' )
)

当我插入一个新元素时，id 值设置为自动递增.

When I insert a new element the id value is set to auto increment.

我的问题是，当我删除元素并插入新元素时，id 值的顺序不再是连续的，而是有洞.

My problem is that when I delete elements and insert new ones the order of the id values is not consecutive any more but has holes in it.

例如:如果我删除关键字段值为 3 和 6 的元素，则值将是:

ex: if I delete elements with key field value 3 and 6 the values will be:

1,2,4,5,7,..等等..

1,2,4,5,7,..and so on..

有没有办法让元素保持正确的顺序?

Is there a way to keep elements in the correct succession?

例如:这样当我删除字段值为 3 的表格元素时，字段值为 4 的元素会自动将其值更新为 3 等等所有其他元素?

ex: so that when I delete the table element with field value 3 the element with field value 4 will automatically update its value to 3 and so on for all other elements?

预先感谢您的帮助！

推荐答案

有什么理由需要在主键序列中没有间隙吗?除了用于唯一标识数据库中的记录外，自动增量主键没有其他功能.允许间隙没有任何缺点(除非您担心键用完，并且看到 int 如何为您提供大约 20 亿个值，您不太可能遇到该问题)，并且尝试不允许有相当大的缺点差距.例如，您从表中删除 ID 为 3 的记录，然后插入一条新记录.这条新记录，因为您不允许存在间隙，所以其 ID 为 3.现在您怎么知道这是原始记录，还是替换了之前删除的另一条记录的记录?

Is there any reason why you need to not have gaps in your primary key sequence? Other than serving to identify a record in the database uniquely, an autoincrement primary key serves no other function. There's no drawbacks to allowing gaps (unless you're worried about running out of keys, and seeing as how int gives you about 2 billion values you're unlikely to run up against that problem), and there's considerable drawbacks to trying to not allowing gaps. For example, you delete the record with ID 3 from your table, then insert a new record. This new record, because you're not allowing gaps, gets an ID of 3. Now how do you know that this is the original record, or one that's replaced another record that was deleted previously?

只有当您的表的主键在其他地方用作外键时，这个问题才会更加复杂.假设您删除记录 3 并将其替换为其他内容，但数据库中另一个表中还有一个条目引用记录 3.该记录的外键是否需要更新以指向其他位置?它应该指向新记录3吗?是否应该与原始记录 3 一起删除?这只是如果您有 2 个带有 1 个外键引用的表.现实世界的数据库往往会很快变得更加相互依赖.

This problem will only be compounded if your table's primary key is being used as a foreign key somewhere else. Suppose you delete record 3 and replace it with something else, but there's also an entry in another table in your database that refers to record 3. Does that record's foreign key need to be updated to point somewhere else? Should it point to the new record 3? Should it be deleted along with the original record 3? And that's just if you have 2 tables with 1 foreign key reference. Real world databases tend to get a lot more interdependent than that very quickly.

做你想做的事根本没有充分的理由.

There is simply no good reason for doing what you want to do.

这篇关于如何在整个删除操作中以连续增长的顺序保持表字段值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！