• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

TIMESTAMP as primary key field ?

 
Timothy Toe
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DB : MySQL 5.0.16

If I need a primary key (PK) that can contain the numbers as large as an INT, I would normally use an INT.

But the problem with INT is that, if the keys are all used up (exhausted), I need to start thinking of ways to reuse �skipped� or �deleted� primary keys. In other words, I need to fill up the �gaps� so as not to waste them.

Another problem is that, if the PKs are all used up, I need to have another timestamp field in the same table to tell me which very old record I can delete to make space for the new record that I want to insert. (Assuming that we can delete very old records. Eg a web email account where, say, an account which stayed dormant for 5 years.)

Since INT has these problems, and since both INT and TIMESTAMP datatypes have a size of 4 bytes, I was thinking, why not just use TIMESTAMP for my PK field instead.

Besides TIMESTAMP is just an integer internally in MySQL, representing the number of seconds since epoch (1970-1-1 00:00:00). It would be as index-able as an INT? Searching through it will be just as efficient? Using it as foreign keys in multiple tables would give no problem?

Is using TIMESTAMP as a PK field OK ? Can anyone tell me whether what I am doing/thinking right or wrong ? Any potential problems ?

Any advice would be very much appreciated.
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You aren't guaranteed that timestamps will be unique. It will typically be the case, but you don't have an guarantees. I think you would also find that an ID generator based on timestamps is slower than one based in integers.

As an FYI I've never seen an app that ran out of integers for a primary key field. Shouldn't even be a practical problem in most applications, because it can suggest either a weak data model (too much unrelated stuff being jammed into one table) or a weak choice of pk (e.g. using a column of semantic relevance instead of a generated id).
 
Paul Clapham
Sheriff
Posts: 21557
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
and since both INT and TIMESTAMP datatypes have a size of 4 bytes, I was thinking, why not just use TIMESTAMP for my PK field instead.
With an INT type you have 2 billion possible keys, or 4 billion if you use the negative numbers as well. If TIMESTAMP is also 4 bytes then there is no way you can have more distinct TIMESTAMPs than distinct INTs.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd also worry about security. By having your primary key be a timestamp you give out hidden information about when the account/data was created that you might not want to.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic