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