how to set primary key after delete a record ?

Abolfazl

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 1,i 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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted 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 < id; 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.
Abolfazl12y ago
thanks for your help.but where i should write this code.i don't know sql server?
Jignesh Trivedi12y ago
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 Sharma12y ago
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