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

From: jefftyzzer <>
Date: Tue, 29 Sep 2009 11:24:01 -0700 (PDT)
Message-ID: <>

On Sep 29, 10:36 am, cm <> 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).
> parentId INT NOT NULL,
> PRIMARY KEY(parentId)
> );
> 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

Yeah, off-hand all that comes to mind is a trigger that fires on DELETEs and does an EXISTS test. Since your PK and, almost certainly, FK columns are indexed, the performance may be acceptable. Which RDBMS are you running?

--Jeff Received on Tue Sep 29 2009 - 20:24:01 CEST

Original text of this message