When should a column be marked as a primary key?

Question

I have a table like this:

// cookies +----+---------+-------------------+------------+ | id | user_id | token | expire | +----+---------+-------------------+------------+ | 1 | 32423 | dki3j4rf9u3e40... | 1467586386 | | 2 | 65734 | erhj5473fv34gv... | 1467586521 | | 3 | 21432 | 8u34ijf34t43gf... | 1467586640 | +----+---------+-------------------+------------+ 

A few days that I think about it. I guess I don't need the id column. Currently, the id column is PK, and also has a unique index in the token column to make it unique and fast to search.

Now I want to know if I can remove the id column from the table and make the token column as PK? This is normal?

Honestly, I have never created a table without an id column (it was always PK) so far. Therefore, it is strange for me to select the token column as PK.

Answer 1

To the extent that token is a wide varchar, I would stick with AI int PK, which you already have. Connections will be faster. So it also inserts. Updates are likely to have the same speed, for example, why update this column to force changes to the index tree. But nesting is faster for child relationships without dragging a wide varchar into the index tree.

This applies to preferences and readability. With regard to readability, there is not much with such a cook. It is not as if it were a category such as “shoes”. This is a miserable, unreadable, inhuman form. Since for readability, there is a small argument in favor of being token like a PC. Of course, sometimes, this can be a little useful.

Additional composites (multi-column indices)

When you start combining PK selections with other peers in composites (extra indices you can choose), a subtle int will become very revealing to be a better choice. Even with moderately large data sets.

Answer 2

In general, we often prefer that the table identifier be the Primary key, but basically says that it should be non-null and should uniquely identify the rest of the table entries (columns), so if you want to make the token as the primary key that you can easy to do, but make sure that it (Id) should not depend on other tables. so whenever you have to fetch any record, you can easily get it using a token.

Answer 3

3 years after you posted this question, I felt that I had to say something. Given that this question arises when other developers with the same problem as yours try to make a decision on such a structured table.

Not to mention many, I want to give you a scenario: imagine that you have a task to manually check if these two values value1 = 1223611547921cvdfr and value2 = 1223611547921cvdfr ... What would you do? Well, the right step is to compare each character between two values ​​from start to finish. And then *seconds later, when you're done, you say, well, these values ​​are the same.

But what happens if you compare these two values, value1 = 2 and value2 = 2 ? For a split second you say "they are the same."

The same scenario happens with computers, complex values ​​lead to an increase in the time of comparison or loading. Often this time, little can be noticed, but what happens if you launch a site such as Facebook, where billions of users enter the network each time?

Thus, SELECT user_id WHERE id = "1" faster than SELECT user_id WHERE token = "dki3j4rf9u3e40..."

Sometimes it’s about simplicity, and simplicity is the preferred way in modern programming. I have seen developers using the wrong methods while they keep saying "this is my preference." Often, what you prefer leads to poor coding, it is important to remain vigilant and look for patterns of modern design as technology develops.