Marc Tardif wrote:
>
> I recommend you read Momjian's online book, very informative and provides
> all the necessary information about using various kinds of id's:
> http://www.postgresql.org/docs/aw_pgsql_book/index.html
In glancing at Momjian's stuff on this just now, I noticed the book
could leave the first-time reader with the impression that both OIDs
and sequences (ie, SERIAL) are viable choices for use as primary keys
(unintentionally?). Sequences and SERIALs are not mentioned until
after a long example of how to do primary keys with OIDs.
My recollection is that OIDs have fatal drawbacks in this context, and
should NEVER be used as primary keys for serious databases. If that
is true, the example of OIDs as primary keys should be removed from
the book, or at the very least put after an example using
sequences/serials. That section appears bound to lead many astray
into the use of OIDs, even though there is a brief section following
on OID "limitations"...
Can anyone confirm/correct that notion?
> As for referential integrity using foreign keys, you can add this
> functionality to postgresql using triggers. An example of this is
> available in the contrib/spi directory, look for refint.*
As Jan Wieck (v7.0 foreign key author) pointed out to me earlier,
concurrent transactions prevent 100% reliable referential integrity
via triggers, at least with pl/pgsql, though you can get pretty close
if you're guarding against the problem scenarios at the application
level. I confess ignorance on refint.*...
Cheers,
Ed Loehr