I have a table, which have an auto-incremented PK and creation_date field, which is the unix timestamp.
I am wondering why not lose the auto-incremented field and use the creation date field as the PK, as it is unique (I am using 1/1000 of a second accuracy).
For: I am killing an indexed row.
Against: there is a slight (very very slight) chance of a duplicate, but it is easy to handle this very rare event.
The DB is mysql.
Because of the size (width) of the index. Timestamps are wide; unless your table contains a bunch of rows, you don't need bigint as the data type of the PK. The thinner the primary key column, the bigger the size of the index chunk you can keep in memory at once, and the faster your queries. So don't do it.
The general answer is that your data may change (where a meaningless id never will)... what happens when you realise that you're storing time in the local zone and DST kicks in? If you want to store against UTC and/or against a specific time zone? For more ordering considerations see wcoenen's answer.
If you start creating 1000's of rows a second, and you're having to mess with data to "make it work" doing something it was not intended for. Perhaps you'd add a disambiguation column that would make your index bigger and slower ...
And then when your project becomes mega popular and people start trying to run reports/queries and "it's using a date as a PK???!!!"
Also consider using a database that allows clustered indexes on non-primary columns.
Because of:
Against: there is a slight (very very slight) chance of a duplicate, but it is easy to handle this very rare event.
You don't have guarantee that your key will be always unique, so that information is not suitable for primary key.
What if you have to insert 10 or 100 records in batch? Would you insert pauses between inserts to be sure that you have unique primary key?
Bad idea, because of "time zones".
If the country hosting your servers observes time changes associated with "Daylight Savings" plans, then once a year the time is going to get set back an hour.
Then for an hour, it will generate duplicate keys.
I worked for a company that had a database with a timestamp key like that, recording thousands of measurements per hour from equipment in a Semiconductor Manufacturing plant. It was developed in Korea (no daylight savings time shifts).
When they installed it here in the US ... we had to shut down the entire factory for an hour every year - in order not to lose the measurements taken during that hour. :-)
Ron
Time is not granular enough, you may end up with insertion failures if two records are inserted at the same time.
For: I am killing an indexed row.
Against: ... in favour of another indexed row that, due to its drastically greater length, will result in significant added overhead if used far more frequently that before.
It is common for PCs or servers to synchronize their clock with a time server. Because of this, you cannot rely on the system clock maintaining a steady pace forward. It may jump backwards or forwards slightly at any time.
Therefore, if you have to be able to reconstruct the order in which your records were created, you'll need a auto-incremented PK. You cannot rely on timestamps. This may sound very theoretical but it has already bitten us.