Auto Increment problem (Identity property)
-
I have a table with field ID int IDENTITY(1,1) (primary key) So lets say I have only two elements, with ID=1 and ID=2. So if admin (id=1) remove element where ID=2 and then add a new element, I will have an element with ID=3 and not with the ID=2. (so table will have two elements one with id=1 and another with id=3 instead id=2) Why this happens?
-
I have a table with field ID int IDENTITY(1,1) (primary key) So lets say I have only two elements, with ID=1 and ID=2. So if admin (id=1) remove element where ID=2 and then add a new element, I will have an element with ID=3 and not with the ID=2. (so table will have two elements one with id=1 and another with id=3 instead id=2) Why this happens?
Maxdd 7 wrote:
Why this happens?
This is how identity columns works.
Best wishes, Navaneeth
-
Maxdd 7 wrote:
Why this happens?
This is how identity columns works.
Best wishes, Navaneeth
-
N a v a n e e t h wrote:
This is how identity columns works.
There is not a way that usually there's no way do alter value of the primary key. So I suppose thats not possible to do something around that, am I right?
Maxdd 7 wrote:
There is not a way that usually there's no way do alter value of the primary key.
:confused: What do you meant by that? Most database systems won't reuse the identity value. It will always increase when new rows are added.
Best wishes, Navaneeth
-
I have a table with field ID int IDENTITY(1,1) (primary key) So lets say I have only two elements, with ID=1 and ID=2. So if admin (id=1) remove element where ID=2 and then add a new element, I will have an element with ID=3 and not with the ID=2. (so table will have two elements one with id=1 and another with id=3 instead id=2) Why this happens?
First of all your question is nothing to do with ASP.NET . This is not the auto increment problem, that is what Identity Works :) For Better Info : Read This[^]
Abhijit Jana | Codeproject MVP Web Site : abhijitjana.net Don't forget to click "Good Answer" on the post(s) that helped you.
-
I have a table with field ID int IDENTITY(1,1) (primary key) So lets say I have only two elements, with ID=1 and ID=2. So if admin (id=1) remove element where ID=2 and then add a new element, I will have an element with ID=3 and not with the ID=2. (so table will have two elements one with id=1 and another with id=3 instead id=2) Why this happens?
IDENTITY columns are designed to work this way. If you want to reuse identity values for some reasons, you can reseed the identity column using:
DBCC CHECKIDENT("Table1", RESEED, 2)
When you insert a new row after executing this statement, your ID column will start from 2 again.
-
I have a table with field ID int IDENTITY(1,1) (primary key) So lets say I have only two elements, with ID=1 and ID=2. So if admin (id=1) remove element where ID=2 and then add a new element, I will have an element with ID=3 and not with the ID=2. (so table will have two elements one with id=1 and another with id=3 instead id=2) Why this happens?
Good Day Maxdd 7 as the guys says , that is how identity works. Delete and Truncate Works Differently. The Delete will continue from the previous seed but Truncate will start from the beginning. lets say i have this Table1 <pre> ============= ID | Name ============= 1 | Vuyiswa 2 | Maxdd 7 3 | Maxdd 8</pre> if i have to Delete ID 3 and later i add a new Record. It will continue as ID 4 but if you truncate(this not a Good idea) the table and add the Records to a Temp table and insert in the very same table from a temp table you will get the records with ordered ID. Hope you understand this.
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
modified on Friday, November 27, 2009 9:12 AM
-
Good Day Maxdd 7 as the guys says , that is how identity works. Delete and Truncate Works Differently. The Delete will continue from the previous seed but Truncate will start from the beginning. lets say i have this Table1 <pre> ============= ID | Name ============= 1 | Vuyiswa 2 | Maxdd 7 3 | Maxdd 8</pre> if i have to Delete ID 3 and later i add a new Record. It will continue as ID 4 but if you truncate(this not a Good idea) the table and add the Records to a Temp table and insert in the very same table from a temp table you will get the records with ordered ID. Hope you understand this.
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
modified on Friday, November 27, 2009 9:12 AM
-
Vuyiswa Maseko wrote:
The Delete will not continue from the previous seed
Did you mean to say "The Delete will continue from the previous seed" ? Because this is what is correct.
Oops i fixed it thanks :)
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
-
Oops i fixed it thanks :)
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
-
You are Welcome :)
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/