Why not use the creation time of a record as a primary key?

Question

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.

Answer 1

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.