Re: RI

Tom Lane wrote:
> Well, it's a little bit more complicated than that. A foreign key
> constraint can only be created when the referenced (primary key) column
> has a unique or primary key constraint. In Postgres, a unique/PK
> constraint always has an associated index. So you're already guaranteed
> an index on that end of the FK. What will not be present, unless you
> create it, is an index on the referencing column. It often is a good
> idea to create that index too, but there are some cases where such an
> index isn't worth its maintenance overhead. You will want such an index
> if you often change or delete entries in the referenced column. If you
> seldom do that, and don't often issue queries on the referencing column,
> then maybe you don't need that index.
>
> regards, tom lane
>
>
Interesting question. When modifying the parent record, Oracle RDBMS
locks the entire child table in shared mode, unless an index on the
child table is present. What does Postgres do in that situation? Can
Postgres somehow locate the corresponding child record(s) without an
index? This feature of Oracle RDBMS was a source of countless deadlocks
during my 20+ years as an Oracle professional. When I come to think of
it, Postgres probably does the same thing to prevent an update of the
child table while the update of the parent table is going on. I confess
not having time to try. Can you elaborate a bit on that?

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com