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 fast for searching.

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

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

It depends on many things. Assuming you're using innodb it comes with a big price. So you need to value and balance pros and cons for your use case.

Downsides of big varchar column:

• Due to the random order of tokens

• your inserts are going to be significantly slower,

• will cause massive fragmentation and

• much more frequent page splits

• Due to this fragmentation the table will actually use you more space than you can save on omitting the the 4 or 8 byte integer key you're using.

• If you have any other keys they are going to contain this big primary key at the end further bloating your table: https://www.percona.com/blog/2006/10/03/long-primary-key-for-innodb-tables/

Upsides of natural primary key:

• By the nature of InnoDB primary key lookups are very fast so if you query your table by only this columns this might give you some edge (although adaptive hash index helps quite a lot with secondary key lookups)

Most of the times people are trying to use natural keys to eliminate the id column for space consideration which is just the opposite of what is actually happening. If you want that for query optimization and that outweighs the downsides than go for it.

StackExchange Database Administrators Q#143008, answer score: 13