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

On Sep 29, 10:36 am, 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

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