Re: [GENERAL] oid as a reference

This is a matter for some debate. Certainly it can be done. IMHO it's
also good. Object ids are a fundamental principle of an OO database and
the OO model in general.

However, it may cause you a few headaches later on. For example Postgres
doesn't yet have a DELETE COLUMN command. The work-around is to do a
SELECT INTO and rename the resulting table. Unfortunately this will
change oids and break your integrity. There are other work-arounds - you
can do an INSERT INTO while selecting the oid, and then do a COPY to a
file etc, but it becomes more painful.

I started off using oids, then I changed to regular ids. Both have
advantages and disadvantages. If all the limitations of postgres are
worked out I may swap back to oids.

Don Yury wrote:
>
> Hi All.
>
> I am just designing my first database in postgres and have a question -
> would it be proper if I use oid for referential integrity? In all
> examples I saw something like
> Create table master(
> id int4,
> ...
> );
>
> Create table detail(
> master_id int4,
> ....
> );
>
> CREATE TRIGGER bt BEFORE INSERT OR UPDATE ON detail FOR EACH ROW
> EXECUTE PROCEDURE
> check_primary_key ('REFB', 'A', 'ID');
>
> But since each object in database have an oid as an indenificator, we
> can:
> [- create master table without id]
> - create unique index on oid for master table
> - in the field master_id of detail table store oid of appropriate master
> object
> - create function check_oid() in order to use it in trigger for
> referential integrity
> Would it be a proper design?
>
> Sincerely yours, Yury.
> don.web-page.net, ICQ 11831432