Tell me more ×
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.
up vote 0 down vote favorite

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.

share|improve this question
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? – dezso Nov 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. – Josh Nov 3 '12 at 13:54

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
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 '12 at 14:49

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.