重新使用自动递增的主键

问题描述：

假设我有一个名为customer的表，如下所示

Suppose i have a table called customer which is as follows

create table customer(
customerid int identity(1,1) primary key,
customername varchar(50),
emailid varchar(30) unique,
address varchar(max),
stateid int foreign key references state(stateid)
)

现在我有一个名为deletecustom的表，就像是

now i have a table called deletecustom which is like

create table deletecustom(
deletionid int identity(1,1) primary key,
customerid int 
)

i需要删除customer表中插入的值的条目deletecustom表

所以我使用像

i need to delete the entry in the customer table for the value inserted in the deletecustom table
so i use a trigger like

create trigger deletecustomer on deletecustom
for insert
as
declare @cid int;
select @cid=customerid from inserted
delete from customer where customer.customerid=@customerid

这将删除customer表中的记录。有没有办法我可以重新使用已删除的customerid字段创建另一个记录？我的意思是，如果我删除customerid为1的客户，我是否可以为其他客户使用customer id = 1才能正常使用？

This deletes the records from the customer table. Is there a way wherein i can re use the deleted customerid field for creation of one more record? What i mean is if i delete a customer whose customerid is 1, can i use customer id=1 for some other customer just for proper utilization?

答

不，你不能直接重用刚刚删除的id。标识字段只是作为增量计数器，您没有任何控制权（除了使用命令 TRUNCATE TABLE 重置它。

No, you cannot directly reuse an id that has just been removed. Identity field just acts as an incremental counter on which you do not have any control (except to reset it with the command TRUNCATE TABLE.

这就是为什么你不应该使用Identity列来显示值。

但你重置了身份值（重置表标识 [ ^ ]）但是当表有大量数据时你不能。在这种情况下你可以使用已删除的Identity值（我的意思是你使用已删除的Identity值创建一个新记录）。使用IDENTITY列插入SQL Server表 [ ^ ]

但我不推荐这种方式。阅读我的答案中的第一行

That's why you should not use Identity columns for Display values.

But you reset the Identity value(Reset Table Identity[^]) but you can't during when table has massive data. During this situation you could use the deleted Identity value(I mean you create a new record using the deleted Identity value). INSERT INTO SQL Server table with IDENTITY column[^]
But I won't recommend this way. Read the first line in my answer

重新使用自动递增的主键

相关推荐