Solved

Cannot Create Identity Key by Altering a Column with Primary Key

Posted on 2011-03-03
2
254 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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