Change Primary Key Values

Hello,
Is there a way in SQL Server 2000 to reassign primary key
values automatically?
For example, I have a table that has a "Person ID" field
as its primary key that was started with an identity seed
of "98753162". The identity increment is "1".
Now, there are hundreds of records in this table. I want
to reassign these PK values with much shorter numbers,
i.e. subtract "98753160" from all existing values. In
addition, I would need all corresponding foreign key
values in other tables to be updated automatically with
the new values.
Thanks,
Mike1)Distable the foreign key constraint with ALTER TABLE command.
look for alter table command in BOL "alter table... nocheck ..." (look for
nocheck clause) which will stop validation of future inserts or updates to
the column.
2) add a column to primary key table and populate the values of PK to this
column with the statement as
update <table> set new_col=PK_col
3)you can update the existing primary key values with the statement such as
update table set PK_col= pk_col - 98753160 --required subtraction.(make sure
primary key constraint is not violated)
4) once this is done you will have to fire an update statement on the FK
tables to update the new FK values replacing the old one.
--
-Vishal
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:038e01c3555a$95864c50$a601280a@.phx.gbl...
> Hello,
> Is there a way in SQL Server 2000 to reassign primary key
> values automatically?
> For example, I have a table that has a "Person ID" field
> as its primary key that was started with an identity seed
> of "98753162". The identity increment is "1".
> Now, there are hundreds of records in this table. I want
> to reassign these PK values with much shorter numbers,
> i.e. subtract "98753160" from all existing values. In
> addition, I would need all corresponding foreign key
> values in other tables to be updated automatically with
> the new values.
> Thanks,
> Mike