Re: foreign keys and transactions

Edwin Grubbs wrote:
> Does anyone know a good solution to determining whether a row is
> referenced by a foreign key? The problem is that multiple tables may have
> foreign keys referencing a single table; therefore, even if you delete a
> given foreign key from one table, the delete on the table with the primary
> key may fail, which will cause the transaction to abort. For example, a
> table of contacts might be referenced by foreign keys in an account table,
> a log table, a group table, and an employee table. If we delete an
> account, we want to try to delete the contact, and if it fails we can go
> on our merry way because it should just mean that it is referenced by
> another table. Querying every single table that could possibly have a
> foreign key referencing the contact seems error prone and a duplication of
> the foreign key checks.
>
> I don't want to just have a separate transaction for each delete from the
> table with the primary key, since that will require placing all the
> deletes after the transaction which contains all the other statements.
> This would make it unbelievably difficult to use functions in our code to
> handle related sql queries, since all the deletes would have to be
> postponed till after the rest of the transaction has finished.

Edwin,

    you  should define for yourself what the different REFERENCES
    from all the foreign key tables mean in your business  model,
    and then declare them accordingly.

    Foreign  keys  in PostgreSQL can have referential actions. So
    you can define per reference, what to do if the  primary  key
    get's  changed  or deleted. For changes (if you allow them at
    all), CASCADE is what you  probably  want,  because  all  the
    references would silently follow.

    But for removal of a primary key, you have to decide what the
    reference should do. Basically you have these options:

    1.  ON DELETE NO ACTION

        Raise an error and abort the transaction. You  know  that
        one too well already :-)

    2.  ON DELETE SET NULL

        Set the reference to the SQL NULL value. This means, that
        the row referencing stays intact,  but  the  foreign  key
        fields get NULLd out.

    3.  ON DELETE SET DEFAULT

        Similar  to  SET  NULL, but the column default values are
        used.

    4.  ON DELETE CASCADE

        The referencing rows get deleted silently.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com