Modify the identity specification of primary key in database

Question

enter image description here I have table "Categories" where primary key is "categoryId". Initially, I made a mistake and did not set identity specification to 'yes'. now I have other tables where 'categoryId' is using as foreign key. I have to identity specification to 'yes' but SQL Server 2005 is not allowing me.

Is there any other option to set it 'yes' except create script for whole database and then make new 'Categories' table?

I also tried to remove primary key from this column but then its saying me that child tables will be deleted.

I tried this one also but its not upto my request.

I mean, through the Properties window-setting Identity specification to Yes? — MusicLovingIndianGirl, Oct 31 '13 at 08:52
Possible duplicate of http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss — R S P, Oct 31 '13 at 09:39

score 0 · Accepted Answer · answered Oct 31 '13 at 08:50

0

try this . go to sql server management studio then : tools -> options -> designers -> Prevent saving changes that require table re-creation (uncheck this )

after this try again to set identity column

answered Oct 31 '13 at 08:50

Besher

550
3
8
29

3

You will lose all of the data if you do this. – Neil Knight Oct 31 '13 at 08:51
@Light : SQL server is not showing me even that option (Prevent saving changes that require table re-creation) by going tools -> options -> designers -> – Sohail Oct 31 '13 at 08:56
@NeilKnight not this will not affect the data. – Besher Oct 31 '13 at 08:58
@Sohail you have sql server management studio installed ? and you can not see this option ? – Besher Oct 31 '13 at 08:59
@Light: SQL is not showing [this highlighted](http://hellowahab.files.wordpress.com/2012/04/disable-prevent-re-creation.png) in my case. – Sohail Oct 31 '13 at 09:07

score 0 · Answer 2 · answered Oct 31 '13 at 09:29

0

If loosing data is no problem means you can retry it by deleting data from your table.

answered Oct 31 '13 at 09:29

Govan

7,751
5
26
42

yup, will try by making dummy database. thnx – Sohail Oct 31 '13 at 09:47

Modify the identity specification of primary key in database

2 Answers2