Solved

Cannot Create Identity Key by Altering a Column with Primary Key

Posted on 2011-03-03
2
249 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
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to set audit trial on sql server 4 45
Trigger for audit 26 71
Get row count of current SQL query 8 45
CROSS APPLY 4 45
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now