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.

DB : MySQL 5.0.16

wouldn't it make more sense to change the datatype of the PK to bigint?

Timestamp might work providing that no one ever changed a record, otherwise the value will change and your realtional integrity will be wrecked if there are any child tables. Equally you can't guaranteee the uniqueness of a timestamp value.

Changing to Bigint should leave the existing data undisturbed and allow for further growth.

It may make sense to prune your data in any event, depends on your business rules, but if someome hasn't traded with / used your services for more than say 12 months do you need to keep their data?

NOT TRUE. You can make a TIMESTAMP value to stay the same even with a update. Use
ts_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP
The column will retain its value for subsequent updates.

I would be interested to know why.

Good point you have there. Thanks. Yes normally you would keep those valuable info for statistical purposes.

My point is, since both INT and TIMESTAMP datatypes have a size of 4 bytes. BIGINT is on another level already.

Why you can't guarantee uniqueness when using a TIMESTAMP as primary key? Well, what happens if you add two records within one second?

And why do you not just use a BIGINT for PK and have a TIMESTAMP field for the timestamp, if you need one? The primary key does not have to be a value that is suitable for anyone but the database to deal with...

Oh sorry, I didn't know that all MySQL time columns (TIME, DATETIME, and TIMESTAMP) does not keep milliseconds. Precision is only up to seconds.


I was thinking...since I already have a timestamp column...and if it can also act as the primary key, then I save an extra unnecessary BIGINT column which is not small (in size) anyway...

Do you think timestamp column as PK does not create gaps thereby making you lose a lot of slots - not to mention you get to start somewhere around the timestamp ID value of NOW instead of starting at 1 ?

Well, it is still possible that two records are added the same millisecond...