3

I am going through a database creation script created by people with more experience than me, and I noticed that the primary keys were set to signed.

So like this,

CREATE TABLE `exampleTable`
(
`pk` INT (11) NOT NULL AUTO_INCREMENT,
`content` TEXT
) ENGINE = INNODB
ROW_FORMAT = COMPACT;

instead of like this,

CREATE TABLE `exampleTable`
(
`pk` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
`content` TEXT
) ENGINE = INNODB
ROW_FORMAT = COMPACT;

It seems to me that by setting the primary key to be unsigned one has doubled the possible number of values, am I missing something where it would actually be better to use a signed int for the primary key?

3

Reasons you may be given include:

  • Suspicion that the unsigned type may not be portable, even among other non-portable code and even if no one's contemplating porting
  • Fear that since it deviates from the default it has the potential to distract, while not being strictly necessary, leading potentially to programmers' waste of time or errors
  • Fear someone may omit unsigned when copying it to other structures (ETL) and not realize the error until eventually the numbers do get over the other half of the range; that may take years and blow out much later
  • Unexpressed, vague fears that something might break, down the line, whether when not handled properly (in one's own codebase), or even if handled properly, if all the rules that make it work aren't clear (in the extreme, this may include fear of an error in the database engine itself)
  • Challenge from seniors, managers, or just people who like to challenge, who are in a position to stop things from happening by challenging them. This group might adduce it's not in the coding conventions to use unsigned, and insist you produce a very powerful reason if you wish to add it to the coding conventions. It won't be easy to prove your reason is strong when you're the only one against others who say otherwise and surrounded by a third group who just don't care.

Or, if you advocate to use the negative half of signed numbers:

  • Dislike of negative numbers / confusion
  • Adduce formatting problems. 'What if these needed to be printed? Would they be printed properly?'
  • Pose a valid question: What happens as the ID assign process has exhausted the negatives and hits 0? Will ID=0 be correctly handled by the application and downstream processes like any other ID's, or will they think it's special?

If you promote things that aren't the norm in a group without building a sound reputation first, you risk people attacking your initiatives and your good reputation will be harder to build and you might even get a bad reputation, even if you have done nothing wrong, or said or caused no difficulty, or been a factor or co-factor of anything gone wrong.

If you contest this, the powers that be will attack you also, and keep you in check, they'll get the 'your attitude' word out and you'll be down in no time ('opinion based' is such another key word they may use when referring to you or your contributions).

The problem isn't negative numbers or unsigned, it's group-thinking, and enforcement of 'normal' by people who set the rules, whoever they are and whether they truly know or not. Of this the database world is full, just as any other technical field where lots of males compete for preeminence and power.

You see, if this was merely a technical problem, it'd be easily solved. I'm not saying easily shared, but at least easily solved. It'd just require your skill, attention, carefulness, thoroughness.

Unfortunately as things depart from the average and even if they are 'better' in a sense, they become a human 'problem'. Now to solve it you need acceptance, patience, negotiation, persuasion, and a tad lots more. Things that are even harder to fix than technical problems.

Departures from the norm can be very good at times, but at times they can shake the ground and test the individual differences and assumptions and presumptions and the group's cohesion and their beliefs and culture and become factors on where the group is going, and its members. The dynamics between the technical and the human are complex, often we won't be able to say what's preferable, short term, long term; we won't even understand it all or have the same opinion.

A little technical issue, recipe, option or preference can be just nothing, or it can become the tip of the iceberg (for a majestic sail, or a ship wreck), or like stroking a genie lamp. We're all a box of surprises and you don't know what's behind the door until you press that button.

3

An INT column can hold: 4,294,967,296 different values.

It doesn't matter if they are signed or unsigned. Obviously you will prefer to deal with positive numbers rather than negative and positive numbers.

If you need more rows then you should change it to BIGINT.

Have a look at MySQL docs:

11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

+---------+-------+-----------------+-----------------+
|  Type   | Bytes |   Minimum value |  Maximum value  |
+---------+-------+-----------------+-----------------+
| Signed  |       |                 |                 |
|   INT   |   4   |   -2147483648   |    2147483647   |
+---------+-------+-----------------+-----------------+
| Unsigned|       |                 |                 |
|   INT   |   4   |        0        |    4294967295   |
+---------+-------+-----------------+-----------------+
1

I think part of it is a degree of magnitude problem,

  • How often do you find problems where 2.1 billion records isn't enough?
  • Out of that pool, how often do you find problems where 4 billion records is enough?

There is a huge need on both sides of the spectrum. But 2-4 billion is kind of an interesting gray area between reasonable and big data. The difference between signed int8 and signed int4 is 9,223,372,034,707,292,160, and only a very small segment of them would be addressed by the extra bit used for the sign.

And, when you get north of 2 billion, often you want some form of sharding and the like so you just simply move to UUIDs.

1

You might have a kludge that has one of these:

  • Negative number are for special cases where the number is outside the normal AUTO_INCREMENT range.

  • You want to flag rows (for some reason) by negating the id. This preserves the value (negate it again to get it back), yet takes it out of the positive range. Caution: In some edge cases, negating the highest id opens up that id for re-assignment via AUTO_INCREMENT.

(In most tables I have ever used, all numbers, not just ids, may as well be UNSIGNED -- because the "business logic" never needs negative integers.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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