Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 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 June 30th, 2007, 05:12 AM
Friend of Wrox
Join Date: May 2005
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default how can i alter table (primary key + identity )

i have the 2 script

one alters the column to have primary key
the other one adds a new id field that is identity
now how can i add both together
so that it alters a field to primary key and identity

alter table test_primarykey
    [ID] ASC

ALTER TABLE test_primarykey add

Old July 12th, 2007, 06:49 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post

First one alters the column to be a primary key, the second one adds a new column with identity set and constraints it as primary key. How can you merge both? In one case the column exist and another the column is added, both the scripts use same column name - ID. Doest it not contradict to what you are trying to do?

Moreover, You can't use ALTER TABLE to modify a column to be identity. It can be done only when adding the column, AFAIK...

So you got to follow the second script. The right things is to, create a new table with identity/primarykey column of similar structure and do an insert into and copy all the rows from old table to the new one.

- Vijay G
Strive for Perfection

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL to identify the primary key in a table? dbayona SQL Server 2005 1 October 24th, 2007 02:33 AM
Update to add identity & primary key seananderson SQL Server 2005 2 July 18th, 2007 09:04 PM
Update Table with dataset without primary key ranakdinesh ASP.NET 2.0 Professional 1 September 28th, 2006 05:30 PM
Alter a column as identity for table kasanar SQL Server 2000 5 July 6th, 2005 08:44 AM

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