Change Field to Identity Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Change Field to Identity Field

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-20 : 14:56:16

I have a table named PERSON and a field named PERSON_ID. How can I set this field to Autonumber? I know I have to use the IDENTITY command, but when I view this field in "design" view, all the IDENTITY options are grayed out.

How can I set this field with the IDENTITY properties?

Thanks

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-20 : 15:05:16

Or even at the creation of the table, this is the line I have:

"[SMT_ID] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,"

How would I modify this line to identify this field as IDENTITY?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 15:05:18

Maybe with

ALTER TABLE ALTER Person_ID INT IDENTITY(1, 1)

???

E 12°55'05.25"
N 56°04'39.16"

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-08-20 : 15:09:13

They may be grayed out because of data type of column Person_ID. Identity can only be specified on tinyint, smallint, int, bigint, decimal, or numeric columns.

Nathan Skerl

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 15:11:42

Then CAST as INT first, and ALTER IDENTITY later.

E 12°55'05.25"
N 56°04'39.16"

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-08-20 : 15:27:00

Or, just create the table using a valid identity data type. Looks like he is still at the create stage, just trying to use a varchar data type.

quote:
Or even at the creation of the table, this is the line I have:

"[SMT_ID] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,"

How would I modify this line to identify this field as IDENTITY?

Nathan Skerl

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-20 : 16:31:14

Im a total Newbie, so can somebosy help me out with what the proper syntax would be?

"[SMT_ID] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,"

Thanks

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-20 : 16:45:00

Disregard-

Proper syntax is:

[SMT_ID] [int] IDENTITY NOT NULL ,

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-08-20 : 16:45:28

Are you familiar with seed or increment of identity?

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources