Re: Problem Deleting Referenced records

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.