1

I very new in SQL Server it's about 3 months. Now, I am stuck with a problem.

I need to change the primary key on a table with lots of data, about 10000 rows. Some other tables have relationship with this table (FK), but some tables stand alone. i wanna change the primary key and change the start identity seed with i want.

I have browsing in google but still no luck.

Can someone in here give me solution.

Thanks..

Frendi
  • 151
  • 1
  • 11

1 Answers1

0

1)To DROP a PRIMARY KEY Constraint

 ALTER TABLE Persons
 DROP CONSTRAINT pk_PersonID

2)Alter table with new primary constarint 

ALTER TABLE Persons
ADD PRIMARY KEY (pk_PersonID)
Rohan Gala
  • 641
  • 5
  • 18
  • Thanks,. can i set custom identity seed with this way ?? by default seed start from 1 but i need start from 1000 for example ?? – Frendi Aug 01 '16 at 09:03
  • You cannot change whether a given column is an identity or not on the fly. If the column is already an identity, you can use `dbcc checkident` to set a different identity seed. If it's not already an identity column, you'll need to drop and re-create the column. – Ben Thul Aug 01 '16 at 14:34
  • Thanks for your advise. actually i need to merge 2 DB with identical structure, but i still stuck in duplicate primary key,, in my mind is i wanna change primary key one DB with increment id start from 5000 we can say DB 1 and let DB 2 no change (DB 2 increment id from 1 until 1000) Or set PK DB 1 as odd (1,3,5,7) and set PK as even (2,4,6,8), and then merge them with generate script. – Frendi Aug 02 '16 at 03:36