Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 13th, 2007, 08:39 AM
Friend of Wrox
 
Join Date: Oct 2006
Location: Northampton, Northants, United Kingdom.
Posts: 114
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update to add identity & primary key

I have a load of tables with an ID column that needs to be updated to an identity and primary key. Reading around, the following suggestion doesn't seem to work. Could someone advise...

ALTER TABLE <table>
ALTER COLUMN <column__name> INT IDENTITY NOT NULL
CONSTRAINT PRIMARY KEY

I am getting

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.

I should add that the tables contain data, including the ID columns.

Regards,

Sean Anderson
__________________
Regards,

Sean Anderson
 
Old July 18th, 2007, 01:55 AM
Friend of Wrox
 
Join Date: May 2006
Location: Delhi, Delhi, India.
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try by providing Identity seed and increment.
ALTER TABLE <table>
ALTER COLUMN <column__name> INT IDENTITY(1,1) NOT NULL
CONSTRAINT PRIMARY KEY

Bijgupt
 
Old July 18th, 2007, 09:04 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

You can't add the IDENTITY property to an existing column... you will need to add a new column with the identity property.

--Jeff Moden




Similar Threads
Thread Thread Starter Forum Replies Last Post
wild & id key comparison and add - discrepancy fin JohnSeito Excel VBA 0 November 27th, 2007 11:32 PM
how can i alter table (primary key + identity ) keyvanjan SQL Server 2000 1 July 12th, 2007 06:49 AM
Update Table with dataset without primary key ranakdinesh ASP.NET 2.0 Professional 1 September 28th, 2006 05:30 PM
can't update the primary key in a gridview hertendreef ASP.NET 2.0 Basics 4 May 30th, 2006 04:04 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.