Re: Problem Deleting Referenced records

Bruno,
thanks. I actually did it that way but having to join two tables each
1-2 million records makes this process rather time consuming.
I was hoping that the ON DELETE options in the constraint could handle
that.

It seems to be a bit odd that if I want to delete 100 records that are
not related to each other, and one record deletion fails that then the
entire delete process fails.

... but I guess there is nothing I can do about.

Alex

Bruno Wolff III wrote:

>On Mon, Nov 10, 2003 at 14:31:00 +0900,
> Alex <alex(at)meerkatsoft(dot)com> wrote:
>
>
>>Hi,
>>I have two tables , A and B where table B has a foreign key constraint
>>to table A.
>>
>>I want to delete all records in table A that are older than a certain
>>date that are not referenced by table B.
>>
>>When I use a DELETE FROM the entire transaction fails as soon as a
>>referential integrity violation is detected.
>>
>>Is that the normal behavior? Is there a way to allow the deletion to
>>complete but skipping all those records that are referenced ?
>>
>>
>
>I think you want to do something like:
>
>delete from A where A.stamp < current_date - '1 month' and
> not exists (select 1 from B where B.Aid = A.id);
>
>It is also possible to join A and B on the delete command using a nonstandard
>syntax, but not exists should be about as fast.
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
>