how to set primary key after delete a record ?

Question

hi i have a table with a column set to primary key and is identify as yes.when i delete a record from table primary key field deleted but primary key steel start from where old data has primary key.for example a record with primary key set to 1i delete it but new record primary key set to 2. i need to set it automatically to 1. i use linq to sql for connect to database and i don't know sql server commands.i want to set it with linq to sql and c#. thanks

Vulpes · Answer

If you want to reuse IDs when records are deleted then I wouldn't use an identity column at all particularly if you're using LINQ to SQL which isn't very flexible when you need to do anything out of the ordinary. I'd generate the appropriate values yourself using C# code and then enter them 'manually' into the database. The code to do this would be on the following lines: private int lastId; private List deletedIds = new List (); private int GetNextId() { if (deletedIds.Count > 0) { int firstDeletedId = deletedIds[0]; deletedIds.RemoveAt(0); return firstDeletedId ; } lastId++; return lastId; } private void AddDeletedId(int deletedId) { deletedIds.Add(deletedId); deletedIds.Sort(); } private void PopulateDeletedIdList(List ids) { int prevId = 0; foreach(int id in ids) { if (id > prevId + 1) { for(int i = prevId + 1; i { AddDeletedId(i); } } prevId = id; } lastId = prevId; } So every time you add a new record call GetNextId to get the ID to use and every time you delete a record call AddDeletedId to add its ID to the list. When the application starts up: 1. Run a LINQ to SQL query to get a list of ids of all the records in the database table sorted by id. 2. Call the PopulateDeletedIdList method with this list as a parameter. Note that the above code will reuse the lowest deleted ID first regardless of the order in which records were deleted.

Jignesh Trivedi · Answer

hi This is normal behavior of Identity column. It will skip the value whenever you delete data from database or sql statement fail to insert new record. Please refer http://technet.microsoft.com/en-us/library/aa933196(v=sql.80).aspx hope this will help you.

Munesh Sharma · Answer

You use use this.. create table tablename { id primary key IDENTITY ( INT  1  2 ) name varchar(50) } IDENTITY ( INT  1  2 ) here IDENTITY means it will start from 1 and take gap b/w id 2

Abolfazl · Answer

thanks for your help.but where i should write this code.i don't know sql server?