Re: Delete all records NOT referenced by Foreign Keys

Martijn van Oosterhout wrote:

>On Sat, Dec 13, 2003 at 09:48:16PM -0600, D. Dante Lorenso wrote:
>
>
>>This is something very ugly indeed and is what I'll have to resort to unless
>>I can find something cleaner.  Ideally, I would be able to run this cleanup
>>on a subset of the table data after an insert into the table.  I would like
>>the query to be fast, though.
>>
>>
>
>What about just:
>
>delete from a where a.id not in (select id from b);
>
>or the equivalent exists query.
>
>
You missed the previous part of the thread.  I have N tables that
have a foreign key to the table in question.  Tomorrow there may be
more or fewer foreign key references.  Without having to know which
tables have foreign keys on my table, I want to delete all rows
that are not used by any any other table.

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;

You see?  Something like what I seek never requires ME the developer or
DBA to know about foreign key relationships because I know that PostgreSQL
already does.

To NOT have this functionality does not cause problems, but it does cause
me to waste disk space on rows that are no longer in use.  I just want to
do some automated cleanup on tables and just leave that process running
in a crontab nightly or something.  I don't want to have to re-write the
cleanup process every time a new dependency is introduced or removed.

I think Bruno had a good idea about using the system tables to determine
relationships, but how to do that is beyond my PostgreSQL expertise at
the moment.  I just think there's gotta be an easier way, though...something
like what I describe above.

Dante

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