Can I use character values ​​for primary keys?

Question

Is there a performance improvement or best practice when it comes to using unique numeric identifier fields in a database table compared to using characters?

For example, if I had two tables:

athlete

id ... 17, name ... Ricky Henderson, teamid ... 28

Command

teamid ... 28, team name ... Auckland

A table of athletes with thousands of players will be easier to read if teamid was, say, “OAK” or “SD” instead of “28” or “31”. Let them believe that teamid values ​​will remain unique and consistent in the form of characters.

I know you can use characters, but this is a bad idea for indexing, filtering, etc. for any reason?

Please ignore the normalization argument, as these tables are more complex than the example.

Answer 1

I would stay away from using text as your key - what will happen in the future when you want to change the team ID for some team? You will have to cascade this key, changing all your data when it is an exact thing that the primary key can avoid. In addition, although I do not have any empirical evidence, I think the INT key will be significantly faster than the text one.

Perhaps you can create views for your data, which simplifies their use, but it uses a numerical primary key.

Answer 2

I believe that primary keys, which are meaningless numbers, cause fewer headaches in the long run.

Answer 3

The text is in order, for all the reasons you talked about.

If the string contains only a few characters, then it will be almost as small as an integer. The biggest potential drawback of using strings is size: database performance is related to how much disk resources are required. For example, if the index is twice as large, it can create excessive disk pressure and increase the number of disk accesses.

Answer 4

I recommend using int or bigints for primary keys. Benefits include:

• This allows you to join faster.
• The absence of a semantic value in your primary key allows you to change fields with a semantic value without affecting relationships with other tables.

You can always have another column for storing command_code or something for “OAK” and “SD”. Also

Answer 5

The standard answer is to use numbers because they are indexed faster; no need to calculate a hash or anything else.

If you use a meaningful value as the primary key, you will have to update it through your entire database if the command name changes.

To satisfy the above, but still make the database readable,

• use a numeric field as a primary key

• immediately create an Athlete_And_Team view that combines the Athlete and Team tables

Then you can use the view when you view the data manually.

Answer 6

I'm just going to curl up with your example. Doug is right when he says that the text is in order. Even for a medium-sized database (~ 50gig) containing a 3-letter code, the primary key will not kill the database. If this simplifies development, reduces the joining to another table, and this is the field in which users will be printed ... I say, go after it. Do not do this if it is simply an abbreviation that you display on the page, or because the athletes table looks beautiful. I think the key is the question "Is this the code that the user will enter, and not just select from the list?"

Let me give an example when I used a text column for a key. I was making medical claims processing software. After the request received all the digitized data, the person had to consider the claim, and then choose a code for him that would indicate what kind of claim. There were hundreds of codes ... and these guys had for them all memorized or bloody sheets. They used the same codes for many years. Using a 3-letter key allows them to simply process claims processing.

Answer 7

Are you talking about your primary key or cluster index? Your clustered index should be the column that you will use to uniquely identify this row most often. It also defines the logical ordering of rows in your table. A clustered index will almost always be your primary key, but there are circumstances in which they can be different.