what's the best way to create a non-sequential primary key of a certain length in MySQL?

I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the object in the database to join a game. I'd like them to be non-sequential so it's not quite so easy to find other games which they aren't necessarily supposed to access.

I've added the criteria that they are a certain length, but I guess I really want them to be at least a certain length.

Could anyone point me in the right direction? I've looked at http://seventhfloor.whirlycott.com/2008/05/27/generating-primary-keys/ but it seems a bit over the top, and I wondered if there were a more straight forward way.

  • Are you sure that the PIN will serve well as a PK? What's its planned length (vs the number of the player-game connections)? How will non-sequentiality prevent random guesses? Nov 2, 2012 at 20:34
  • There's nothing to stop random guesses but it's harder to guess when it's not sequential. The constraint is that it needs to be very easy to join a game once you have a pin, i.e. no password. This obviously makes it easier for anyone else to guess a pin. Just how it is.
    – Josh
    Nov 3, 2012 at 13:54

1 Answer 1


I would suggest that the "right direction" would be to go a different direction entirely.

A sufficiently random and difficult-to-guess distribution of keys, assuming that is even an achievable objective, is likely to be otherwise sub-optimal for space and performance. But that's the second problem.

The first problem is that exposing the internals of your database in such a fashion is intrinsically unsafe.

Instead, create a separate column for this "PIN" -- of whatever desired length and data type, with a unique key constraint on the column so no more than one record can possibly have the same PIN associated with it.

You can then populate this value using whatever algorithm you want, and you can subsequently change the algorithm, change the min/max lengths of the value, etc., without any other impact on the rest of your application or database as a result of such changes.

  • Ah I see, I guess I was trying to skip a step by making the pin the primary key as it would ensure uniqueness. I'll look for a non-SQL way of generating the pins.
    – Josh
    Nov 5, 2012 at 14:49

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.