Re: Constraints/On Delete...

On Sat, 4 Jan 2003, Boget, Chris wrote:

> > You don't alter first. You need to drop the constraint on
> > second and add a new constraint on second with on delete
> > cascade.
>
> Could you give me an example of this? This is what I tried:
>
> ALTER TABLE "second" ADD CONSTRAINT "secondfk"
> FOREIGN KEY (record_num) REFERENCES "first"("record_num")
> ON DELETE CASCADE
>
> which gave me this error:
>
> ERROR: secondfk referential integrity violation - key referenced
> from second not found in first

I think you want
ALTER TABLE "second" DROP CONSTRAINT "$1";
ALTER TABLE "second" ADD CONSTRAINT "secondfk"
FOREIGN KEY (name) REFERENCES "first"("record_num")
ON DELETE CASCADE;

> Also, from the looks of it, it seems like what will happen is
> when records from "second" are deleted, the referenced records
> in "first" will be deleted, too. Is this the case? If so,

No. Referential actions are applied on changes to the primary key side of
the constraint (in this case "first"), so deletes from first cause
actions on second.