Re: timestamp as primary key?

On Oct 19, 2006, at 10:30 , John D. Burger wrote:

> cckramer wrote:
>
>> I have table for online chat system that keep messages sent
>> between users.
>
>> Question: is it okay to use timestamp as primary key, or there is
>> possibility of collision? (Meaning two processes may INSERT into
>> table
>> within same millisecond.) It is a web application.
>
> tometzky wrote:
>
>> If your insert fail you can always try again after some random short
>> time.
>
> But then the timestamp field does not accurately represent the
> actual time of the event. If you really want a primary key, and
> you really don't want to just use a sequence default, I would make
> the key a composite:
>
> PRIMARY KEY (user_id_from, user_id_to, message_time)
>
> This should cut way down on the possibility of key collision.

Only if each message is contained in its own transaction since now()
is effectively a constant throughout a transaction. In this case, I
would choose a surrogate key since it is likely that the table will
be referenced.

-M