CODEWITHSUNDEEP
sqldatabase-design
Joe
Joe

Reputation: 311

random string as surrogate key

I'm a beginner SQL user here and trying to develop good design skills. One question I have is what to use to primary keys. Right now, my code generates 8-character random strings (letters and numbers) and uses that when creating a new record. For subsequent records, it generates another random key, checks if it already exists in the DB, then either uses it (if it doesn't exist) or generates a new one and repeats. Is there ANY value to this method versus just using an auto-increment column? I really have no particular reason to use this method, but I could swear I read about someone using something like this for primary keys - just can't remember why.

Upvotes: 0

Views: 344

Answers (2)

Jay
Jay

Reputation: 27474

The only reason I've ever had to use a random string for a key is when I want to have a high probability that a record generated in one instance of a database will not have the same key as a record generated in another. In that case I use a GUID, which is basically a randomly-generated string that is long enough that the probability of getting a duplicate within the next billion years is tiny. I've used that in cases where, for example, we are creating records on a staging database and also on a live database, and we want to copy data from the staging DB to the live DB without overwriting data created directly on the live DB.

As @Rabbit says, there could be security issues. Besides the one he mentions, suppose you assign sequential numbers to records, and a user sees that he gets a URL of /read_secret_data.php?id=132. He might well try /read_secret_data.php?id=131 and see what he gets. If the data really is secret, you should, of course, have security beyond hoping that no one will guess a valid ID, like passwords. But still, why make it easier for the hacker than you have to?

All that said, I almost never use a randomly generated key. Mostly it just adds extra pain for no good reason.

Upvotes: 1

Rabbit
Rabbit

Reputation: 507

Do you have some need for security through obfuscation? The key can leak some data. Mainly the relative time when a record was inserted into the table. Knowing this, you can make inferences about some of the data. For example, if it's a table of births, you might be able to guess some ages based on the key. If you need to anonymize the data, that's usually when there's a need for a random key.

If not, just use an auto-increment.

If you do, it's better to use a number for a key. Plus, you haven't said which DBMS you're using but some of them come with a method to generate a random numeric key.

Upvotes: 3

Related Questions