CODEWITHSUNDEEP
sqlsql-server-2008
Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

Zero As Primary Key In SQL Server 2008

Is it possible to insert 0 in the primary key field of a table in SQL server 2008?

Upvotes: 2

Views: 3262

Answers (2)

user365004
user365004

Reputation: 41

Yes, it can be zero. The value can be from −2,147,483,648 to 2,147,483,647, from −(2^31) to 2^31 − 1, the full range of an unsigned integer.

If you expect a lot of records, like up to 4.3 billion, it makes sense to start from the smallest value, and work your way up. 

CREATE TABLE TestTable
(
    TestColumn INT IDENTITY(−2,147,483,648, 1) NOT NULL PRIMARY KEY --start at 1
)

Upvotes: 0

Ian Henry
Ian Henry

Reputation: 22403

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.

Upvotes: 6

Related Questions