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.

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.

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?
–
dezsoNov 2 '12 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.
–
JoshNov 3 '12 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.