Re: [HACKERS] Another RI question

Andreas Zeugswetter wrote:

>
> >     Is it ALLWAYS the case, that a FK constraint refers to the PK
> >     of  another  table?  Or could arbitraty attributes of another
> >     table be referenced by a FK too?
>
> arbitrary (usually unique indexed) columns

    NOOOO!  It  will be too bad if the referenced PK isn't unique
    indexed!  An ON DELETE CASCADE constraint will fire a trigger
    to  delete  all the rows where FK equals deleted PK. But this
    shouldn't happen if PK isn't guaranteed to be unique, instead
    it must check if another row with same PK still exists.

    And  it is absolutely damned for the DELETE,INSERT situation.
    How should I be able to see that this happened  and  suppress
    the triggers on DELETE/INSERT though? I think I can't.

    Thus, the sequence

      BEGIN;
      DELETE PK;
      INSERT same PK
      COMMIT;

    where  FK's  with ON DELETE CASCADE exist will delete them if
    the constraint has  been  set  to  IMMEDIATE.  No  chance  to
    prevent   except   we   add   a   non-standard  feature  "NOT
    IMMEDIATEABLE" to constraints so these triggers will  allways
    be fired at transaction commit.

    And  the INITIAL DEFERRED trigger doing the ON DELETE CASCADE
    must check if at the time  it's  called  really  no  such  PK
    exists  any  more.   These  generic RI-trigger proc's will be
    sophisticated, man.

>
> >     Is it guaranteed that I find the PK  definition  of  a  table
> >     allways in the index <tablename>_pkey?
>
> No. I think there is a column in pg_index that marks a pk already.
> (for odbc) This would imho be the best way.

    Ah - yes. It's pg_index.indisprimary - thanks.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #