Thread: OID's as Primary Keys

"Richard Teviotdale" <richard@satcomresources.com> writes:
> Is an Object Identifier (OID) is a good choice for a primary key within a d=
> atabase table?

The received wisdom is "no".  Please see the pgsql archives for many
prior discussions of this topic.

            regards, tom lane

The use of the OID as a primary key does not
facilitate the use of relational data models;
primarily because it doesn't mean anything.  For
example, if I have a table of people who are defined
as unique by the ssan (Social Security Number) field,
I would still need to create a unique index on the
ssan field.  Furthermore, I would think that working
with OIDs as foreign keys would be difficult at best.

Best regards,

Andrew Gould

--- Richard Teviotdale <richard@satcomresources.com>
wrote:
>
> Is an Object Identifier (OID) is a good choice for a
> primary key within a database table?
>
-----------------------------------------------------------------------------------------------------------
> Lets explore this question...
>
> OID's are created automatically, so even if I create
> a specific INTEGER field within my table as a
> primary key (PK), I will end up with an OID anyway.
> Thats got to be an waste of some space.
>
> I would need to specify the -o option to the pg_dump
> command, when performing backups. Otherwise you
> would loose all your PK's.
>
> Because you cannot SERIALIZE OID's, Invoice numbers
> that run successively would not be possible
> (Although OID's are successively derived by the
> database, they are assigned first come, first served
> to whichever table an INSERT specifies).
>
> After an INSERT the OID property is available
> imediately, allowing code efficiency. Ironically,
> this same OID propery is used to get the new
> record's PK, using an additional SQL statement, if
> you don't use the OID as the PK.
>
-----------------------------------------------------------------------------------------------------------
> I hope this spurs some discussion about...
>
> Other than lacking sequental PK's, are there other
> disadvantages with the OID PK choice?
>
> Does the use of OID PK's limit the maximum number of
> records allowed throughout the entire database?
>
> Are there any issues with restoring a database from
> a pg_dump file?
>
-----------------------------------------------------------------------------------------------------------
> Thank you
>


__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com