Nithesh Nithesh - 6 months ago 45
SQL Question

Zero As Primary Key In SQL Server 2008

Is it possible to insert

in the primary key field of a table in SQL server 2008?


As long it's a numeric field, yes... follow along at home!

create table TestTable
    TestColumn int not null primary key

insert TestTable values(0)

The primary key restriction only requires that the value be unique and the column not be nullable.

For an identity field:

create table TestTable
    TestColumn int identity(1, 1) not null primary key --start at 1

set identity_insert TestTable on
insert TestTable (TestColumn) values (0) --explicitly insert 0
set identity_insert TestTable off

The identity(1, 1) means "start at one and increment by one each time something is inserted". You could have identity(-100, 10) to start at -100 and increment by 10 each time. Or you could start at 0. There's no restriction.

You can generally answer questions like these for yourself by just trying them and seeing if they work. This is faster and usually more beneficial than asking on StackOverflow.