Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
first off, whoever is downvoting without leaving feedback, you're doing a disservice. Next up, the answer to this excellent question. –  jcolebrand May 23 '11 at 21:20
1  
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. –  RolandoMySQLDBA May 23 '11 at 21:48
add comment

3 Answers 3

up vote 10 down vote accepted

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.

share|improve this answer
    
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 –  Garet Claborn May 23 '11 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 May 23 '11 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. –  Jack Douglas May 24 '11 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 May 24 '11 at 6:33
    
"What if you wanted to have a users table, and indicate that the ones with negative numbers were system controlled" - sounded like an endorsement to me. If it wasn't meant to be perhaps you could re-word and I'll be very happy to remove the down-vote. –  Jack Douglas May 24 '11 at 6:49
show 7 more comments

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.

share|improve this answer
    
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. –  Jack Douglas May 24 '11 at 6:26
    
@JackPDouglas: you'd use NOT FOR REPLICATION to avoid generating values on the wrong site –  gbn May 24 '11 at 6:55
    
along with check constraints? Also, is there a MySQL (or other) analog of NOT FOR REPLICATION that you know? –  Jack Douglas May 24 '11 at 7:06
    
@JackPDouglas: sorry, not sure about non SQL Server –  gbn May 24 '11 at 7:45
    
+1 for giving a practical use of unsigned primary keys. –  StanleyJohns May 25 '11 at 14:41
add comment

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.

share|improve this answer
    
Yup, I think we've covered that ground before ;) tehehe dba.stackexchange.com/questions/983/… –  jcolebrand May 24 '11 at 14:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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