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.

  • Apart from the answers, you may find the following link informative, as well: agiledata.org/essays/keys.html - it describes the pros and cons of surrogate keys vs natural keys. – Joishi Bodio Jul 5 '16 at 15:46
  • This table seems to be temporary in nature. Would you even have any use case with access by surrogate key? Should be primarily by "token", on login by "user_id" and from some scheduler by "expire". You require the unique index on "token" in any case, there should not be any benefits in speed or size adding a surrogate key. And finally, even if you update "token", there should be nothing to propagate to. I'd recommend an index (token) organized table. – Thaylon Jul 6 '16 at 12:56
up vote 11 down vote accepted

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:

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.

  • Good answer. Even as a unique index it will fragment but not have exact effects. – paparazzo Jul 5 '16 at 15:34
  • Something your answer doesn't mention is the case when you need to apply a unique constraint on the natural key anyway. (This would be done for data integrity.) I'm not sure about MySQL, but in most DBs, this entails a behind-the-scenes index anyway. How would that situation impact your answer? – jpmc26 Jul 5 '16 at 16:52
  • Also consider foreign keys. Any children of the cookies table will need a column as large as the token column, which could potentially eat more space if that column is large than what you are using for the id Column. – David Baucum Jul 5 '16 at 22:17
  • @jpmc26 Yes, in MySQL is the same. Unique constraint implies an index. That doesn't impact the answer since it has no effect how data is stored (of course index nodes are stored in that order for obvious reasons). Data in InnoDB is stored in B+Tree based on the PK so in terms of data node fragmentation the PK is the normative. – Károly Nagy Jul 6 '16 at 7:36
  • @DavidBaucum Yes, very good point. – Károly Nagy Jul 6 '16 at 7:36

If the table has natural primary key that will not change then it is appropriate to use it as the PK

If it is long some times you might use an ID as the PK for performance

As covered in good detail in the answer from Nagy. Since it is out of order it will fragment. The unique index will also fragment.

If you use the natural key then the FK is going to be longer. But now in a query you don't have to join back to this table to get the natural key.

This question might head towards a bit of a religious war as you are basically asking about natural vs surrogate primary key.

There are all sorts of different considerations that might be relevant and many different people will have many different opinions on what is the best option in any given case. A google search will find enough articles and posts about the topic to keep you busy reading them for ages.

Personally I like using a natural key, when one is available, but when in doubt I'll always use a surrogate key. Remember the primary key has certain characteristics that are required or at least a good idea. It has to be unique. It shouldn't need to be changed ever if at all possible. It can't be optional. It can be helpful if it is compact. Be careful when considering this because somethings that might seem like natural keys at first don't actually work well in the long run. For example SSNs might seem like a good option, if you ignore the fact that you should be encrypting them, because they'd seem to be unique, static and permanently tied to specific user but that isn't actually the case. The same SSN can be issued to multiple people, rare but can happen, you also have cases of identify theft or potential fake SSNs used by illegal immigrants. You could also end up merging data from another system, say you buy out or are bought out by a competitor, and you might have someone in both systems but might not want to combine their records together.

Ideally we'd need more details to make a recommendation. I'm assuming User_id is a foreign key and that is why you picked Token over User_ID as your replacement primary key. If the relationship is a One to One relationship, each user can only have one entry in the table and one token, you could still use the FK as the PK. In that case one could also ask why these fields aren't simply on the Users table.

If you've never used a natural key on a single table odds are you've had some unnecessary surrogate keys but this example might not be the best example of a natural key for your system.

As Joe Celko never tires of noting, the concept of Primary Key is an artifact of pre-Relational database technology. As such it is wholly within the Physical Design of your database. At the Logical Design level it is necessary to have a Unique constraint on the column token to preserve the integrity of your data, as that column is a Natural Key for your table.

Then the question is (a) whether or not to have an additional surrogate key to assist in performance of Foreign Key lookups; and (b) if yes, then should the table be clustered on the Natural Key; the Surrogate Key; or in some cases on neither (heap storage, using an implied RowID as surrogate Key). In only one case will it be necessary to specify as part of Logical Design that a Surrogate Key be present in the table - when it must be allowed for the Natural Key to be editable.

These two questions are not part of the Logical Design of your database, so need not be addressed early if your development methodology allows for convenient schema updates to your DB.

As an aspect of Physical Design, the answers to these two questions will be properly based on the estimated table sizes, and the frequency of FK lookups into this table. In order to make a sensible design one should be well read on the affect of index structure and construction over time.

It is unfortunate that the term Primary Key, which originates as an artifact of pre-relational database technology, was first co-opted into the Logical domain by Date, Boyce, Codd, et al; and then co-opted back into the Physical domain by the major RDBMS vendors. I avoid the term completely when I can, and specify all my indices as being simply Unique or Non-Unique, and decide which one will be clustered as late in the development cycle as I can.

  • 1
    Good answer. Codd's 1970 paper "A Relational Model of Data..." stated that there could be more than one primary key and that the preference for one key over any other was arbitrary. It seems that he changed his mind and later made too much of the primacy of one key over another. To be fair to Date, he was one of those who pointed out that it wasn't necessary always to single out just one key (see Relational Database Writings 1991-1994). Possibly primary keys were Codd's first real mistake (nulls were a bigger mistake but came much later). – nvogel Jul 7 '16 at 9:58

Other answers have addressed the main issues. There are two side issues worth noting. The first is mutability and the second is trustworthiness.

It is usually necessary, or at least very desirable, to have the primary key be immutable. If it can be modified at will, and if it's the target of recorded references, then the modification has to be cascaded to all of the references, or else references will be orphaned. Foreign keys are the most obvious case of recorded references, but they aren't the only case.

For most databases, auto-increment integers are immutable, and in cases like yours that's a plus.

Natural keys are sometimes mutable, and are sometimes used as identifiers. Many websites that support identified users use email address as the userid. This works out well in practice. Most users have an email address, they are supposedly unique, and the user can remember it. So the user references their account this way, even if the account has an internal id that is merely a meaningless number. When a user changes their email address, they are going to want to change their userid at most websites, in order to cut down on how much they have to remember. If the website won't allow that, because the userid is marked as a PK in the database, that's a data management problem.

Second, when you rely on natural data for the row identifiers, you get into trouble when they are wrong, due to error or deceit. For example, when you hire employees, there is always the possibility that you will hire someone who gave you a false social security number. If you later hire someone with that social security number, now you are up the creek. I don't know your case well enough to say whether this consideration applies here.

  • Why "up the creek" if you hire someone with a duplicate SSN? To block the insertion of a duplicate so that the matter can be properly resolved seems like a reasonable response in those circumstances. Are you suggesting you should not enforce uniqueness in order to make it easier for people to commit fraud? – nvogel Jul 8 '16 at 6:04
  • No, that's not what I'm suggesting. – Walter Mitty Jul 9 '16 at 5:43

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.