OID's as Primary Keys
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
"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