What are negative keys used for?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.