SQL primary key can accept '0'?

Question

SQL Server 2008 / 2005

I am confused between Primary and Unique Key.

I am aware that Primary Key will not allow NULL, while Unique will accept NULL. Also, a table can have 'n' number of unique keys ..!

Whether Primary Key will allow Zero ?

Answer 1

Primary Key Can be Zero, but if you set Identity on the column it normally will start at 1 rather than Zero.

Answer 2

A primary key can allow 0 depending of course on the datatype of the Primary key. An autogenerated PK (Identity) can start at 0 but that is not the default behavior, you will have to set it up to start at 0 (heck you can start with negative numbers if you want). For best results this should be done before you start entering data to the table.

What you cannot do is have multiple records with a 0 as the PK as that violates the uniqueness requirement of the PK.

If you have existing data and want a record that has a value of zero for a specific purpose (for instance we put in a user for our import process, so the insertedby field could show that the record came from an import), then what you probably want is to allow a manual insert into the identity field, so you can enter this one specific record at the value you want, then return to the usual setting. This is done this way:

set Identity_insert dbo.table1 ON
insert dbo.table1 (id, myfield)
Values (0, 'test')
set Identity_insert dbo.table1 OFF

Do not do this in application production code, this is a system admin type of task that should only be done occasionally for either the setting of a particular record outside the scope of the normal data or to insert records transferring from another source (when you know their existing id values are currently unused). This should not be lightly used to get around the restrictions of an identity field. In particular it should never be used to fill in records where the identity skipped (due to a deletion or rollback) as this defeats the purpose of using an identity and can cause data integrity issues if proper PK/FK relationships were not set up.