Alter Column with Identity

Question

Post reply

Alter Column with Identity

Pulivarthi Sasidhar

SSCertifiable

Points: 6706
More actions
August 19, 2010 at 1:14 am

#239170

Hi All,
The following one is the Table definition.
After inserting some rows ...i want to add identity property to Owner_id.
So please give me alter statement.
CREATE TABLE [dbo].[Testowners](
[owner_id] [int] NULL,
[owname] [varchar](15) NULL
)
INSERT INTO Testowners
Select 1,'asas'
Select * from Testowners

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 1

Hello,

I get a script similar to below when I tried your requests using SSMS

I hope that helps,

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Testowners

(

owner_id int NOT NULL IDENTITY (1, 1),

owname varchar(15) NULL

)

GO

SET IDENTITY_INSERT dbo.Tmp_Testowners ON

GO

INSERT INTO dbo.Tmp_Testowners (owner_id, owname)

SELECT owner_id, owname FROM dbo.Testowners

GO

SET IDENTITY_INSERT dbo.Tmp_Testowners OFF

GO

DROP TABLE dbo.Testowners

GO

EXECUTE sp_rename N'dbo.Tmp_Testowners', N'Testowners', 'OBJECT'

GO

COMMIT

This solution requires creation of a new table which is same as initial table except that will have an identity column set while it is being created

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Krishnraj SSCrazy Points: 2027 More actions · Answer 2

-> Open table design in SSMS.

-> Select "Owner_Id" column and see the property at bottom side.

-> You can find "Identity specification". set it to "Yes".

That's it.

Lowell SSC Guru Points: 323518 More actions · Answer 3

I'd recommend the method Krishnraj suggested; this is especially true with big tables with lots of constraints or foreign keys or other dependancies....the GUI does all the grunt work behind the scenes.

for reference, you cannot alter a column to turn on the identity property once it has been created. you have to rebuild the table, which is exactly what the gui would do behind the scenes.

the GUI will do the following:

· build a new table to the correct specification

· migrate the data fromt he old table to the new.

· drop all the constraints against the original table

· recreate all the constraints against the new table

· drop the original table

· rename the new table to the original name

you can ADD a NEW column with the identity property, but not alter an existing:

CREATE TABLE [dbo].[Testowners](

[owner_id] [int] NULL,

[owname] [varchar](15) NULL

)

--fails: not allowed to alter an existing and add the identity property

ALTER TABLE [Testowners] ALTER COLUMN [owner_id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY

--allowed , if the column didn't exist

ALTER TABLE [Testowners] ADD [owner_id2] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 4

Hi,

-> Open table design in SSMS.

-> Select "Owner_Id" column and see the property at bottom side.

-> You can find "Identity specification". set it to "Yes".

Thanks for Info.....

It wont work.....It will ask for Dropping of object & Re-Create of the same object.

Regards,

Pulivarthi

Krishnraj SSCrazy Points: 2027 More actions · Answer 5

Hi,

For that follow the below steps:

-> Go to "Tools" menu and click on "Options"

-> In the option window, click on "Designers".

-> Here now uncheck the checkbox which says: Prevent saving changes that require table re-creation

But i want to say one thing as Lowell said in the previous post that the GUI does lot of grunt work behind the scenes.

So I recommend u follow it.

Thanks Lowell for sharing that information with us. Thank u very much...:-)

celal85 SSC Enthusiast Points: 103 More actions · Answer 6

alter table [ishchiler] alter column [ishchi_kod] [int] identity(1,1)

problems

Incorrect syntax near the keyword 'identity' how to problems?

help me e-mail adress celal85@mail.ru

Lowell SSC Guru Points: 323518 More actions · Answer 7

note: two year old thread.

the answer is right here, so re-read the whole thread, and use the GUI instead;

your error is mentioned in my post above:

--fails: not allowed to alter an existing column to add the identity property

ALTER TABLE [Testowners] ALTER COLUMN [owner_id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY

[/code]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!