Dropping an auto numbered primary key and add a new one

The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table.

I need to write SQL to drop the current primary key and add a new one Say "RecordId"

as the new primary key and which should be a autonumber too.

any idea.

thanks in advance

droping and adding a primary key on a column wont affest its 'identity' property, so simply drop the primary key and add again with the new name u want...

alter table tablename drop constraint oldpk

alter table tablename add constraint newpk primary key(id)

|||

What's the point? It is easy enough, just drop the column and add another one, but there might be an easier way to do what you are wanting to do.

Here is a script that does it:

set nocount on
drop table test
go
create table test
(
testId int identity constraint PKtest primary key,
value datetime default (getdate())
)
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
insert into test default values
go
select *
from test
go
alter table test
drop PKtest
go
alter table test
drop column testId
go
select *
from test
go
alter table test
add testId int identity (2,2) constraint PKtest primary key
go
select *
from test --the identity values will start at 2 and go up by steps of 2 (so you can see the diff)

sql