Problem:
I have an enum database table with name DataTypes
Id Name
--------------------
1 short
2 Int
3 long
and in c-sharp Classes i have
enum string DataTypes{short=1,Int=2,long=3}
and we used these references in other tables and classes.
Now we got a scenario where we have to add more datatypes
Id Name
-----------------------
1 short
2 Int
3 long
4 Chara
5 string
6 float
If we delete chara from table and try to add again chara on 4 it is not possible we will
get 7 Id for new chara element.
But we have kind of scenario where we want it to add with 4 Id value.
Solution:
Here we have three approaches we can use whatever is suites best to us:
1. Use Truncate:
First what we can do if we are allowed to Truncate tables than Truncate it.
Truncate table tablename
After truncating table id will get reset to 0 but it will work if we dont have child table.
If we have child parent relationship then first we need to delete referenced record from
child table and then only we can truncate table.
2. Reset Identity Value:
Second way is to reset it directly using following command:
DBCC CHECKIDENT('TableName', RESEED, [NewValue])
If for new insert value we want Id [new_Id] then :
[NewValue]=[new_Id]-1
for eg.:
In above case we want new value to 4 then reset command will be:
-- for example
DBCC CHECKIDENT('DataTypes', RESEED, 3)
3. Set Identity OFF(before Insert Statement) and ON (after Insert Statement):
In this solution we do it in three phase
- Set Identity OFF for Table
- Run Insert data script but we need to specify Value of Id field explicitily
- Set Identity ON for Table
For e.g:
Set identity Off
Insert into DataTypes (Id, Name)
values (4, 'Char')
Insert into DataTypes (Id, Name)
values (5, 'String')
Set Identity ON
This is my findings through R&D on Identity Column.
Happy Living...
Happy Cocepts...
Happy Programming..
