On Mon, Nov 10, 2003 at 14:31:00 +0900,
Alex <alex@meerkatsoft.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.
