如何在SQL数据库中将ID的连续序列恢复为主键？ [英] How to restore a continuous sequence of IDs as primary keys in a SQL database?

问题描述

我正在使用SQlite数据库和Django的 QuerySet API访问此数据库。我将数据顺序写入数据库，每个条目都有一个简单的ID作为主键（这是Django的默认值）。这意味着数据库中现在有一个连续的ID序列（条目1具有ID 1，条目2具有ID 2，依此类推）。现在，我需要再次删除一些条目。这意味着ID序列现在是不连续的（条目1具有ID 1，但条目2可能具有ID 3、8、1432或其他任何东西，但不是2）。

I'm using a SQlite database and Django's QuerySet API to access this database. I wrote data sequentially into the database and each entry has a simple ID as the primary key (this is the Django default). This means that there is a continuous sequence of IDs now in the database (entry 1 has ID 1, entry 2 has ID 2, and so on). Now I needed to delete some entries again. This means that the sequence of IDs is discontinuous now (entry 1 has ID 1, but entry 2 might have ID 3, 8, 1432 or anything else, but not 2).

如何再次恢复该连续的ID序列并将它们与数据库中的其余条目相关联？是否可以使用Django的 QuerySet API做到这一点，还是需要使用普通的SQL？我没有使用普通SQL的经验，因此在这种情况下，一些有效的代码将非常有帮助。谢谢！

How can I restore this continuous sequence of IDs again and associate them with the remaining entries in the database? Is there a way to do this with Django's QuerySet API or do I need to use plain SQL? I have no experience with plain SQL, so some working code would be very helpful in this case. Thank you!

推荐答案

我想不出任何需要这样做的情况。最好的主键是不可变的（尽管这不是技术要求），使用无意义的整数主键的目的的目的是避免必须对其进行更新。

I cannot think of any situation in which doing this would be desirable. The best primary keys are immutable (although that's not a technical requirement) and the very purpose of using non-meaningful integer primary keys is to avoid having to update them.

我什至可以说，如果您需要一个有意义的，不间断的整数序列，请在表中创建一个单独的列，保留主键及其序列中断，然后重新编号新的序列

I would even go so far as to say that if you require a meaningful, unbroken sequence of integers, create a separate column in your table, keep the primary key with its sequence breaks, and renumber the new "sequence" column when needed.

但是，您可能有我想不到的要求。如果您确实 需要更改这些键中的值，请确保对数据库中该列的所有引用均受FOREIGN KEY约束保护，并检查 ON UPDATE CASCADE 选项。

However, you may have requirements that I can't think of. If you really need to change the values in those keys make sure that all the references to that column in your database are protected by FOREIGN KEY constraints and check out the ON UPDATE CASCADE option when you declare a foreign key. It will instruct the database to do the updating for you.

但是如果您不必这样做，就不要。

But if you don't have to this, don't.

这篇关于如何在SQL数据库中将ID的连续序列恢复为主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！