Mysql – Would there be a reason not to make primary keys unsigned

database-designMySQL

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?

Best Answer

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.

Related Question