Primary Key falling short

Plz tell me what to do if Primary key fall short and execed the max size of
the datatype like integer with Identity increment option.If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:
> Plz tell me what to do if Primary key fall short and execed the max size of
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.sql

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?