Solved

Cannot Create Identity Key by Altering a Column with Primary Key

Posted on 2011-03-03
2
253 Views
Last Modified: 2012-05-11
Hi,

I have a table created with primary key not null and it has data in it.
Later on i realized the primary key column should be identity (1,1).

I have to do it using the scripts not going to the design view.

ALTER TABLE XYZ
ALTER COLUMN [ID] [int] IDENTITY(1,1) NOT NULL;

i get the following error, any help will be appreciated.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IDENTITY'.

Thanks,
Rohan

0
Comment
Question by:Star79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
kemi67 earned 250 total points
ID: 35028331
You can't alter the existing columns for identity.
You have 2 options,
1. Create a new table with identity & drop the existing table
2. Create a new column with identity & drop the existing column


This is an example for a table XYZ and fileds ID and Fielda

 
/* Per evitare potenziali problemi di perdita di dati, si consiglia di esaminare dettagliatamente lo script prima di eseguirlo al di fuori del contesto di Progettazione database.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_XYZ
	(
	ID int NOT NULL IDENTITY (1, 1),
	fielda nchar(10) NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_XYZ ON
GO
IF EXISTS(SELECT * FROM dbo.XYZ)
	 EXEC('INSERT INTO dbo.Tmp_XYZ (ID, fielda)
		SELECT ID, fielda FROM dbo.XYZ WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_XYZ OFF
GO
DROP TABLE dbo.XYZ
GO
EXECUTE sp_rename N'dbo.Tmp_XYZ', N'XYZ', 'OBJECT' 
GO
COMMIT

Open in new window


other examples here
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032/
0
 
LVL 2

Assisted Solution

by:Michael-Thomas
Michael-Thomas earned 250 total points
ID: 35076150
kemi67 is correct.  

You can generator your own SQL from SSMS by going to design on your table, changing the ID field to identity then choose the save script option from the icons at the top left.


If you need to reseed the identity value you can use this

declare @maxID int
select @maxID = max(ID) from XYZ
DBCC CHECKIDENT (XYZ, reseed, @MaxID)

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 80
How to use TOP 1 in a T-SQL sub-query? 14 54
LAG_ROWID - how do I get the right order using this query? 2 28
How come this XML node is not read? 3 53
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question