Re: deleting a row potentially referenced by many rows in another table

From: TroyK <cs_troyk_at_juno.com>
Date: Tue, 29 Sep 2009 11:19:55 -0700 (PDT)
Message-ID: <40d3087c-8b5d-4d0e-9a7c-089a8de203c9_at_w36g2000yqm.googlegroups.com>



On Sep 29, 12:36 pm, cm <cmonthe..._at_yahoo.com> wrote:
> I have a situation that is somewhat the opposite of the case in which
> you would use ON DELETE CASCADE on a foreign key in a child table to
> ensure that when a row in the parent table was deleted, the delete
> would cascade to the matching rows in the Child table (see example
> below).
>
> CREATE TABLE Parent (
> parentId INT NOT NULL,
> PRIMARY KEY(parentId)
> );
>
> CREATE TABLE Child (
> childId INT NOT NULL,
> PRIMARY KEY(childId),
> FOREIGN KEY (parentId) REFERENCES Parent(parentId)
> );
>
> In my case, I want to delete a row in the parent table when the last
> row in the child table referencing that (parent) row is deleted (N:1
> from child to parent). Is there any way to do this automatically?
> Otherwise, it seems that it would be necessary to check the Child
> table each time a delete is performed to see if any rows still
> reference the same parent row and then delete it (the parent row) if
> none are found.
>
> Thanks,
> CM

What is the meaning of the presence of a row in Parent at first insertion (i.e., before any "children" are entered) that is not the same meaning later on after all of its children are deleted? Received on Tue Sep 29 2009 - 13:19:55 CDT

Original text of this message