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(at)debis(dot)com (Jan Wieck) #