stack stack - 5 months ago 17
SQL Question

When should I mark a column as the primary key?

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 to
id
column. Currently
id
column is PK and also I have an unique-index on
token
column to make it both unique and fast for searching.

Now I want to know, can I remove
id
column from the table and make
token
column as the PK? Is that normal?

To be honest, I've never created a table without
id
column (it's always been the PK) so far, So that's weird for me to choose
token
column as the PK.

Answer

To the extent that token is a wide varchar, I would stick with the AI int PK that you already have. Joins will be faster. So too will inserts. Updates would likely be the same speed, as, why would that column be updated thus forcing index tree changes. But, inserts are faster for the child relationships by not dragging the wide varchar into an index tree.

It comes down to preference and readability too. As for readability, there is little of that with such a varchar. It is not as if it is a category like 'shoes'. It is a miserable unreadable non-human form. So as for readability, there is little argument for having the token as PK. Granted though, at times, it may be slightly useful.

Additional composites (multi-column indexes)

When you start combining the PK of choice with other fellow columns in composites (additional indexes you may choose to have), the thin int will become very apparent to be the best choice. Even with moderately large datasets.