Re: Delete all records NOT referenced by Foreign Keys

Stephan Szabo wrote:

>On Sun, 14 Dec 2003, D. Dante Lorenso wrote:
>
>
>
>>PG already can block a delete when it knows that foreign key exists, so
>>why can't I perform a query that says...
>>
>>    DELETE FROM tablename
>>    WHERE FOREIGN_KEY_EXISTS(oid) IS FALSE;
>>
>>
>
>That's fairly different from the checks that are performed for the foreign
>keys which happen after the action has happened and errors to prevent the
>action from being visible.  The where clause happens long before that.  If
>the above has to check each referencing table for matching rows for each
>row in tablename, I'd also expect it to perform poorly.
>
>
My hope was that there was some sort of (semaphore? / counter?) associated
with each row that indicated whether a dependency existed at all.  Although
that would most likely not be an indexed column, I could apply additional
WHERE constraints to avoid a full table scan.

If such a counter existed, it would certainly speed up deletes when no
foreign
key checks were necessary.  But I suppose it would also slow down
deletes when
it becomes necessary to decrement a counter for the dependencies created
by the
row being deleted.

So...internally, how does a row KNOW that it can not be deleted because of
a foreign key constraint?  Whatever that mechanism is, isn't there a way for
me to make use of that as I try my query?

And... if not...how about something like this in PL/SQL:

    -- Find all the address records which might need to be removed...
    FOR my_rec IN
        SELECT addr_id
        FROM address
        WHERE acct_id = in_acct_id
        AND addr_is_active IS FALSE
    LOOP

        -- try to delete this record...
        DELETE FROM address
        WHERE addr_id = my_rec.addr_id;

    END LOOP;

This will loop through the records one at a time and try to delete them.
However, I don't want to have any exceptions thrown if the DELETE action
can not be performed.  Is there a TRY/CATCH type of code that I can
surround the DELETE with to prevent the entire operation from being
aborted on the first error found?

Dante

----------
D. Dante Lorenso
dante@lorenso.com