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(at)Yahoo(dot)com #

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