Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet.

When and why is it useful to have negative (or rather signed) keys indexing a table?

  • 7
    first off, whoever is downvoting without leaving feedback, you're doing a disservice. Next up, the answer to this excellent question.
    – jcolebrand
    Commented May 23, 2011 at 21:20
  • 2
    This subject is intriguing. I have personally never heard of this concept. This question should never have been downvoted. +1 from me for introducing this concept. Commented May 23, 2011 at 21:48

3 Answers 3


All a primary key is is a value that we have determined is the value that is of utmost importance in a record. Whether that key is a signed int, an unsigned int, a string, a blob (actually, there are limits) or a UUID (or whatever name it takes today), the fact still stands that it is a key, and that it is the thing of utmost importance.

Since we're not constrained to use only positive oriented numbers for our keys, it makes sense to consider that a signed int will only go to ~2 billion, whereas an unsigned int will go to ~4 billion. But there's nothing wrong with using a signed int, setting the initial value to ~ -2 billion and setting an increment of one. After ~2 billion records you'll hit "zero" and then you'll continue to ~2 billion.

As to why it would be helpful to have "negative keys" in a table, that's the same question as "why is it helpful to have keys in a table". The "value" of a key has no impact on its status as a key. A key is a key is a key.

What is important is if the key is valid.

As to why it would be useful to allow keys that were negative, I can suggest some reasons:

What if you wanted to indicate returns in a sales system as negative sales order numbers, that matched the positive sales order number, thus making correlation easy (this is naive, and poorly designed, but it would work in a "spreadsheet" sense).

What if you wanted to have a users table, and indicate that the ones with negative numbers were system controlled (SO does this very thing, for chat feed users).

I could go on, but really the only reason why the number being negative is of importance is if you or I assign importance to it. Aside from that, there is no great reason for the value of a key to have any bearing on the key itself.

  • Edited out the 'niche occurrence' bit, as it is more than likely a misunderstanding due to inexperience. Interesting read. I was somewhat imagining there would be a situation where the negative value of the key was somehow useful in coding (i,e without deciding it means a particular thing) but this is very useful thinking when you have a strong division into two groups and don't want to use an extra bool :p Commented May 23, 2011 at 21:41
  • 1
    @Garet ~ So you make a good point: "the value of the key was somehow useful in coding" and I do make use of my keys from time to time in just that manner, but that has nothing to do with the database aspect. The database is a storehouse. The application that consumes the data, on the other hand, cares about the values. But yeah, that +/- boolean is a neat trick, I've seen it used many a time for just such an effect.
    – jcolebrand
    Commented May 23, 2011 at 22:02
  • 2
    -1 for implying that dual purposing values like this is anything other than a bad idea. You need an int and a bool? Use an int and a bool. Commented May 24, 2011 at 6:24
  • 1
    @JackPDouglas I don't recall encouraging people to do that, I strictly recall implying that the field and it's data are two different things. Thanks for at least offering constructive feedback on your downvote, but I can't say that that observation means anything in light of the concept of the "what are negative keys used for" since that is an application logic problem, not a database layer problem. I did want to highlight how those things are used in the application layer, but in the database layer they don't have any significance.
    – jcolebrand
    Commented May 24, 2011 at 6:33
  • 1
    @JackPDouglas ~ I used that particular example because there is a very well known site (network of sites) that you may have heard of that does that very thing, so I don't want to be dismissive of it, because it is a valid "trick". Check out this user dba.stackexchange.com/users/-1/community and tell me what his ID is. I can almost assure you that the userid is the primary (and in many other tables a foreign) key. Just because it's bad application design to you, doesn't mean that it's not valid. But once again, that's not db design, that's domain design. Granted, the db logic supports it
    – jcolebrand
    Commented May 24, 2011 at 14:28

If we're on about identity or autonumber columns, the value itself should have no meaning. (sometimes it does, as per SO's chat users nentioned by drachenstern, which I've done before myself)

However, generally you'd lose half of your range if you're using signed integers.
See: What to do when a field in a table approaches the max signed or unsigned 32 bit integer?

Another example: In small replication scenarios, using negative values for one site and positive for another gives some implicit knowledge of the source of any given row.

  • and make sure you are somehow constraining the values input at each site otherwise you will end up in a horrible mess when your "implicit knowledge" turns out to be wrong. Commented May 24, 2011 at 6:26
  • @JackPDouglas: you'd use NOT FOR REPLICATION to avoid generating values on the wrong site
    – gbn
    Commented May 24, 2011 at 6:55
  • along with check constraints? Also, is there a MySQL (or other) analog of NOT FOR REPLICATION that you know? Commented May 24, 2011 at 7:06
  • @JackPDouglas: sorry, not sure about non SQL Server
    – gbn
    Commented May 24, 2011 at 7:45

Not all database systems even support unsigned integer types, MSSQL being one of those that doesn't. In these cases negative values are possible in integer key fields simply because they are possible in the type (you could use rules or triggers to block them, as shown in this example, but there is probably no need add the overhead of enforcing such rules to every inters/update).

As far as the database is concerned the actual value of a primary key does not matter as long as it is unique within the table. To it -42 and 42 are just two different numbers in the same way 42 and 69 are - meaning will only be imparted on the negativeness or not of the value by your code.

Not supporting unsigned integer types is probably a design decision based on reducing complexity - i.e. not wanting two different 32 bit integer type to worry about checking ranges on when assigning values between them. It does limit the number of indexes possible in an auto increment field starting an 0 or 1 to half what would be possible in an unsigned type (~2e9 rather than ~4e9) but this is rarely a significant issue (if you are likely to need a number of key values of that magnitude you probably went for a 64-bit type anyway especially if using a 64-bit architecture where such values are processed no less efficiently then 32-bit ones) though if you might want the full range and need to stick to 32-bit for space reasons you could start the increment at -2,147,483,647.

  • Yup, I think we've covered that ground before ;) tehehe dba.stackexchange.com/questions/983/…
    – jcolebrand
    Commented May 24, 2011 at 14:40
  • tinyint is unsigned (0 .. 255). I usually create a check constraint on integer columns to ensure values are nonnegative, since inevitably code will be written that implicitly assumes it and strange bugs will arise if somehow a negative value creeps in.
    – Ed Avis
    Commented Aug 16, 2015 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.