How To Create Table with Identity Column

Question

I have an existing table that I am about to blow away because I did not create it with the ID column set to be the table's Identity column.

Using SQL Server Management Studio, I scripted a "Create To..." of the existing table and got this:

CREATE TABLE [dbo].[History](
    [ID] [int] NOT NULL,
    [RequestID] [int] NOT NULL,
    [EmployeeID] [varchar](50) NOT NULL,
    [DateStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

My question is, how would I modify this SQL so that my resulting table has the ID column set as the Identity?

[ID] [int] NOT NULL IDENTITY(1,1) – Li0liQ May 23 '12 at 18:27 — Li0liQ, May 23 '12 at 18:27

score 167 · Accepted Answer · edited Apr 25 '14 at 11:55

167

CREATE TABLE [dbo].[History](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [RequestID] [int] NOT NULL,
    [EmployeeID] [varchar](50) NOT NULL,
    [DateStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
) ON [PRIMARY]

edited Apr 25 '14 at 11:55

quadfinity

877
1
8
9

answered May 23 '12 at 18:28

Gratzy

9,164
4
30
45

1

I believe that is what I was looking for. I don't need to change the `PK_History` value to `ID` or anything? – May 23 '12 at 18:36
No that should be all you need, PK_History is just the name of the constraint the primary key constraint is on the column ID already – Gratzy May 23 '12 at 18:40
1

what is the meaning of the parameters IDENTITY(1,1) – otc Jul 31 '17 at 16:17
4

Identity(seed,increment) https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property – Gratzy Jul 31 '17 at 16:19

score 43 · Answer 2 · answered May 23 '12 at 19:19

43

This has already been answered, but I think the simplest syntax is:

CREATE TABLE History (
    ID int primary key IDENTITY(1,1) NOT NULL,
    . . .

The more complicated constraint index is useful when you actually want to change the options.

By the way, I prefer to name such a column HistoryId, so it matches the names of the columns in foreign key relationships.

answered May 23 '12 at 19:19

Gordon Linoff

1,242,037
58
646
786

5

OP is specifically working with the output of a GUI tool that outputs the DDL for an existing object. That tool probably doesn't have, a "use simpler syntax if possible" option. Least error prone, for OP's specific situation, would be to edit the one line in the generated DDL, and not try to write it from scratch using simplest syntax. Also the example you gave does not assign a name to the PK constraint as OP has. Many prefer my constraints named so they have the same name in all environments (dev, test, prod). `ID int constraint PK_History primary key identity(1,1)` I believe. – Shannon Severance May 23 '12 at 19:31
3

Maybe, maybe not. I've been in situations where I've taken the script from one database, used it on another server, and the defaults that worked in one place were not best for the other. In any case, I only suggested this as a solution since it seems simpler to me (I personally understand the "primary key" keyword much better than I understand the options on a constraint and I consider using options I don't understand as "bad"). However, you make a good point about why another solution might be preferred. I should add, that answer is already the accepted answer. – Gordon Linoff May 23 '12 at 19:46
@GordonLinoff: Mate, primary key is by default not null, why you need to explicitly specify not null here? – Jasmine Dec 19 '18 at 22:00
@Learner . . . Both constraints were in the OP's code. The `NOT NULL` is redundant. – Gordon Linoff Dec 19 '18 at 23:10
1

This answer was helpful for me, because the OP's case was not the same as mine and this was the first question that google turned up – James Hurley Jul 29 '22 at 17:51

score 13 · Answer 3 · answered May 23 '12 at 18:27

13

[id] [int] IDENTITY(1,1) NOT NULL,

of course since you're creating the table in SQL Server Management Studio you could use the table designer to set the Identity Specification.

enter image description here

answered May 23 '12 at 18:27

Phil

42,255
9
100
100

1

Well, thanks Phil. I know how to do that, but I can't because the table is already there. I've got to delete the table and recreate it, hence why I am using the script. – May 23 '12 at 18:35
5

@jp2code: I meant that you could have created a test table with an Identity column and then scripted that to see how it should be specified. – Phil May 23 '12 at 18:40

score -3 · Answer 4 · edited Mar 29 '16 at 10:42

Unique key allows max 2 NULL values. Explaination:

create table teppp
(
id int identity(1,1) primary key,
name varchar(10 )unique,
addresss varchar(10)
)

insert into teppp ( name,addresss) values ('','address1')
insert into teppp ( name,addresss) values ('NULL','address2')
insert into teppp ( addresss) values ('address3')

select * from teppp
null string , address1
NULL,address2
NULL,address3

If you try inserting same values as below:

insert into teppp ( name,addresss) values ('','address4')
insert into teppp ( name,addresss) values ('NULL','address5')
insert into teppp ( addresss) values ('address6')

Every time you will get error like:

Violation of UNIQUE KEY constraint 'UQ__teppp__72E12F1B2E1BDC42'. Cannot insert duplicate key in object 'dbo.teppp'.
The statement has been terminated.

I needed to make the `ID` column **unique**, not the `Name` column. Why have you added the **unique** constraint to the `Name` column? How does that benefit you in your `teppp` table? — , Mar 29 '16 at 11:15

How To Create Table with Identity Column

4 Answers4

Linked

Related