SQL Server中的“重置身份”列 [英] Reset Identity column in SQL Server

问题描述

我正在建立一个存储联系人的SQL数据库。我希望能够删除联系人，每个联系人的正确ID对我连接到它的软件至关重要。可以说我已经联系了詹姆斯，他是第一个。他的ID为0。我添加Mary的ID为1。如果删除James，如何将Mary的ID设置为0，而不是保持1？由于她现在是第一位，因此必须重新设置。换句话说，当有人被删除时，如何重置数据库中的所有ID？谢谢

I am making an SQL database that stores contacts. I want to be able to delete contacts, and the correct id for each contact is crucial for my software connecting to it. Lets say I have contact James and he is the first one. His id is 0. I add Mary and her id is 1. If I delete James, how can Mary's id be set to 0 instead of staying 1? It has to reset since she is the first one now. In other words, how can I reset all of the IDs in the database when someone gets deleted? Thanks

推荐答案

在自动递增的主键列上执行此操作没有任何意义，即使这样做很琐碎，如果不对相关表进行批量更新，则会影响数据完整性。为此，您可能需要从列中删除索引和主键约束（此时您的应用程序可能会崩溃），对所有后来的记录重新编号，对所有相关表重新编号，然后重新应用主键约束和索引。

It makes no sense to do this on an auto-incrementing primary key column, as even if it was trivial to do, without mass updates in related tables you affect your data integrity. To do so you would likely need to drop the index and primary key constraint from the column (at which point your application may flake out), renumber all later records, renumber all related tables, then re-apply the primary key constraint and index.

如果您确实必须具有某种形式的线性标识符，且始终以0开头（这可能表示软件设计存在问题），那么您可以使用辅助ID列除了主键外，您还可以使用以下语句更新主键，以将较高的值向下移动到梯级中：

If you really must have some form of linear identifier which always starts at 0 (this may then indicate a problem with the software design) then you can have a secondary ID column in addition to the primary key, which you then update to shuffle higher values down a rung with a statement such as:

UPDATE table
SET secondaryID = secondaryID - 1
WHERE secondaryID > (SELECT secondaryID FROM table WHERE primaryID = [id to delete]);

DELETE FROM table
WHERE primaryID = [id to delete];

我强烈反对这种做法-如果您的ID由于删除了记录而缺少值，软件应该测试这些值的存在，而不是仅仅假装出来。

I strongly discourage such a practice - if your IDs are 'missing' values because of deleted records, the software should test for existence of these values rather than just wigging out.

这篇关于SQL Server中的“重置身份”列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！