Re: UUID column as pimrary key?

On Tue, 4 Jan 2011 11:07:00 -0800 (PST), Dennis Gearon
<gearond(at)sbcglobal(dot)net> wrote:
> I haven't been able to find anywhere, easily, in the documentation
> using google
> where a list of allowed data types for primary keys is.
>
> So, UUIDs can be primary keys?
> Any issues wtih them on sorting or paging of index tables, etc.?

Disadvantage
* aren't ordered in way as records are added to DB.
* 128bit length (PSQL stores them as 128bit value)
* slower to generate you need to use random number generator
* ... if you do select * on table with two uuids you will need to
scroll GUI to see data :)
* ... really unhandy if you want to make manual updates :)

Advantage:
* simple to generate, and 128bit random is almost globally unique,
* you have your id, before executing query, (in contrast to all this
autoincrement) so you may put it in dependant rows
* almost every platform has UUID generator

Advantage / disadvantage
* depending on UUID generator, UUID can store some "privacy"
information e.g. MAC address of your card, such UUID.

Personally I prefer pooled incremental id's. Fast, unique, you have Id
before query - but you need to write "code" by self.

> Also, the documentation says that UUIDs are 128 bit value, but never
> explicitly
> says that's how it's stored. Nor does it use one of the nice, blue
> headered
> tables for UUID (or ENUM) showing storage and other attributes as it
> does for
> numeric, character,boolean, date/time, binary, monetary, geometric,
> or network
> types.
>
>
>
> Dennis Gearon
>
>
> Signature Warning
> ----------------
> It is always a good idea to learn from your own mistakes. It is
> usually a better
> idea to learn from others’ mistakes, so you do not have to make them
> yourself.
> from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036'
>
>
> EARTH has a Right To Life,
> otherwise we all die.