0

When writing my thesis, I was writing a part about SQL data types and how to choose them wisely when designing a database structure.

I read somewhere that as a best practice you should not assign negative values to PK. this lead to the following question:

will an instance of SQL Server assign negative values to a Primary Key by default ? I know it is possible to assign them yourself, but I was wondering if SQL server will assign them by default and if so, in which cases?

gotqn
  • 42,737
  • 46
  • 157
  • 243
David Defossez
  • 11
  • 2
  • Although uncommon, I wouldn't go so far as to say negative primary key values is a practice to be avoided, identity or not. Can you provide the link to where you read this, or the rationale? IMHO, one should first choose data types most appropriate for the domain of data stored, and then choose which columns are the primary key. – Dan Guzman Apr 17 '15 at 11:41
  • @DanGuzman I think the reasoning was something like "if a signed `int` isn't enough to store all your IDs, unsigned `int` isn't going to be enough either". But yeah, taking over rules and ideas without also remembering the references and reasoning is a very bad idea :D – Luaan Apr 17 '15 at 11:52
  • @Luann, yea, references are good for a thesis:-) I agree that negative values simply to increase capacity should generally be avoided. Negative values are sometimes used to double capacity for legacy applications that require an int data type, but a bigint would be appropriate for new development. – Dan Guzman Apr 17 '15 at 12:19
  • @DanGuzman: here is the article, sorry for the late response ! [link](http://www.brandonsavage.net/designing-databases-picking-the-right-data-types/) I fully agree with you, that you should avoid negative values to increase capacity. when doing some research I read a post on this forum where someone said, It would not be wise to use negative values because, although SQL server might be able to handle them well, an application working with this data would not handle them as well, for instance ASP.NET. – David Defossez Apr 20 '15 at 07:07
  • @Luaan: this is why I do research before writing stuff down :P – David Defossez Apr 20 '15 at 07:08
  • @DavidDefossez, I work with a application that uses negative identity values and they are handled properly in Microsoft SQL Server and ASP.NET. Much depends on the coding techniques used. Some ADO.NET objects may use negative values for nascent rows. Note the referenced article is speaking of PHP and there is no mention oft SQL Server. It does mention MySQL does not allow negative PK values. – Dan Guzman Apr 20 '15 at 11:42
  • @DanGuzman, indeed the article is not directly related to SQL server but it did raise this interesting question. thank you for the assistance ! – David Defossez Apr 21 '15 at 09:31

2 Answers2

0

I assume, but I've never tried, that if you create a primary key with a designation of IDENTITY(0,-1), it will auto assign descending values starting from 0

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
-1

There lies a difference between Primary Key and Identity.Identity Property creates an identity column in a table with syntax:

IDENTITY [ ( seed , increment ) ]

where increment is the incremental value that is added to the identity value of the previous row that was loaded which can be + or - depending on the requirement.

and Primary Key is a column or combination of columns that contain values that uniquely identify each row in the table. It can be any set of columns other than the Identity column if they garuntees unique identification of rows in the table.

Coming back to the question a table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.

So SQL server don't assign any default value to a primary key as it's a constraint. When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns.

Deepshikha
  • 9,896
  • 2
  • 21
  • 21