Primary Key Auto Increment

Hi, my table's primary key is int(4), auto increment with the seed 1, if I got 3 data inside, means the id should be 1, 2, 3

id fname lname

==============================

1 Stephen Saw

2 Ali Baba

3 ABC DEF

suppose new record will have id of 4. Let say I deleted data with id 3, before adding new record, the new id will be 4 instead of 3, how can I made the new id to become 3 instead of 4 after delete the id 3's data. Is it possible to insert new record with id of 2 if I delete the Ali's record, but it will continue with id 4, or is there any method for me to reorder the id? Thanks in advanced.

u can do it , but its not advisable, as it will require extra processing...

still...u can either write a script to find the smallest possible id which is free... and insert at that...

or..dont actually delete the key..mark it as deleted....say update the name as deleted..then find the smallest id with name 'deleted' and add the new record there using update instead of insert....

these r a few turnarounds..u may find more.....

|||

If it is only for last row then you can use the following query,

create table mytable
(
id int primary key identity(1,1),
name varchar(100)
)

Insert Into mytable values('Test Data 1');
Insert Into mytable values('Test Data 2');
Insert Into mytable values('Test Data 3');
Insert Into mytable values('Test Data 4');

Delete From mytable Where id=4

IF IDENT_CURRENT('mytable') = (Select max(id) from mytable)
Begin
SET IDENTITY_INSERT mytable OFF;
Insert INto mytable values('Inserted Implicit');
End
else
Begin
SET IDENTITY_INSERT mytable ON;
Insert INto mytable(Id,name) select max(id) + IDENT_INCR('mytable'),'Inserted Explict' From mytable;
SET IDENTITY_INSERT mytable OFF;
End

|||As has been said, we could write you some clever script that does this. There will be a lot to it.

AND

If something goes wrong somewhere your going to have a nightmare. What if an order was placed by someone with ID of 4, then you wanted to delete it and 5 became 4... you would have to accound for moving the ID of the orders too or your data would be very washy.

To be honest I would highly reccommend against it.

Let us know how you wish us to progress this further.|||You DON'T.

The identity is a unique identifier, NOT a sequential number.

Why do you care?