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
Dropping an auto numbered primary key and add a new one
davehomebrew
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)
pnp
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)